Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-03-2022, 08:02 AM
mda99das mda99das is offline Help with lookup / v lookup big failure Windows 7 64bit Help with lookup / v lookup big failure Office 2013
Novice
Help with lookup / v lookup big failure
 
Join Date: Oct 2022
Posts: 12
mda99das is on a distinguished road
Default Help with lookup / v lookup big failure

I have enclosed a sample workbook.

What I want is a look up function that looks up the reference number and returns all the data in the other columns as described.
I keep getting error messages.
I have tried vlookup and lookup but it doesn't help.
Sometimes I get the error cannot search on the left.


It sounds simple in theory but its driving me insane.
Thank you.
Attached Files
File Type: xlsx Copy of TRIAL FTAS (1).xlsx (18.0 KB, 6 views)
Reply With Quote
  #2  
Old 11-03-2022, 09:43 AM
mrgramm mrgramm is offline Help with lookup / v lookup big failure Windows 10 Help with lookup / v lookup big failure Office 2021
Novice
 
Join Date: Jan 2020
Posts: 7
mrgramm is on a distinguished road
Default

Change booking date from form sheet to a table. Move Reference number to right.
In cell b2 of Daily Scan sheet enter:
=INDEX(Table1[First name of child],MATCH($A2,Table1[[Ref number]:[Ref number]],0))
Should be able to drag this to all cells.
Reply With Quote
  #3  
Old 11-03-2022, 10:02 AM
ArviLaanemets ArviLaanemets is offline Help with lookup / v lookup big failure Windows 8 Help with lookup / v lookup big failure Office 2016
Expert
 
Join Date: May 2017
Posts: 762
ArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to behold
Default

I don't have Office in my home comp, so I can't see, what you have, but based on your posting some advice:
1. Let's name the table where you look for info as source table. The column containing the value you are looking up for (reference number) must be leftmost one in range you refer to (i.e. at least all other columns you want to get info from must be at right of reference number column there);
2. Lookup value (reference number) columns in both of your tables must be both text, or both numbers. NB! having column formatted as number or text doesn't guarantee that the values there are same format! To ensure this, in case numeric formatting, you can multiply all values in column with 1 (enter 1 into empty cell formatted as General, copy it, select the column with data, and use PasteSpecial.Multiply on this column). In case text formatting, enter into some empty column a formula like ="" & A2 (edit cell address depending your table), copy this formula down, select the range with formula, copy it, and use PasteSpecial.Values to overwrite values in original column. Or simply format some empty column as text (helper column), copy your original data and use PasteSpecial.Values to write copied data into helper column. Then format original column as text (in case this was not done before), and copy tata there over with data from helper column (to be on safe side, use PasteSpecial.Values again).

When the formatting of reference number is OK in both of tables, you can start with writing formulas in target table. Let's assume in both tables the reference numbers are in column A, and datarange starts from 2nd row (in 1st row are table headers). Let's assume you want to get values from 3rd column of source table into your target table. Into some free cell in 2nd row (you have yourself decide the column you want this info to be) you enter the formula like
Code:
=VLOOKUP($A2, SourceSheetName!$A2:$X2, 3, 0)
and copy the formula down. You have what you wanted there!

NB! With last parameter equal 0 the formula searches for exact match in source table. When the exact match is missing, the formula returns an error.

You can use parameters -1 or 1 instead - then the formula returns the last value the search was nearing at (in ascending or descending order), before the change direction was reversed, or the value got greater/less. With those parameter values the formula will work properly only, when the source table is ordered properly by search column - otherwise you can get any row form source table matching with row in target table, depending the order of reference numbers!

Last edited by ArviLaanemets; 11-04-2022 at 09:38 AM.
Reply With Quote
  #4  
Old 11-04-2022, 07:29 AM
p45cal's Avatar
p45cal p45cal is offline Help with lookup / v lookup big failure Windows 10 Help with lookup / v lookup big failure Office 2019
Expert
 
Join Date: Apr 2014
Posts: 695
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

Quote:
Originally Posted by ArviLaanemets View Post
Code:
=OFFSET($A2, SourceSheetName!$A2:$X2, 3, 0)
and copy the formula down. You have what you wanted there!
Shouldn't that OFFSET be VLOOKUP?
Reply With Quote
  #5  
Old 11-04-2022, 09:38 AM
ArviLaanemets ArviLaanemets is offline Help with lookup / v lookup big failure Windows 8 Help with lookup / v lookup big failure Office 2016
Expert
 
Join Date: May 2017
Posts: 762
ArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to behold
Default

Quote:
Originally Posted by p45cal View Post
Shouldn't that OFFSET be VLOOKUP?
My bad! Corrected!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
LOOKUP - Complex lookup with 2 lookups in 1 cell sglandon Excel 6 05-05-2016 09:44 AM
Help with lookup / v lookup big failure Lookup Tony Singh Excel 3 03-06-2015 11:03 AM
Lookup angie.chang Excel 1 07-27-2012 09:45 PM
Help with lookup / v lookup big failure Possible Lookup Karen222 Excel 3 01-10-2012 05:41 AM
Help with lookup / v lookup big failure LookUp aztiguen24 Excel 5 05-24-2011 03:57 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:10 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2023, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2023 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft