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

Copy of TRIAL FTAS (1).xlsx (18.0 KB, 6 views)

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.

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
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!

Shouldn't that OFFSET be VLOOKUP?

My bad! Corrected!