Vlookup function with more than one sheet?

Question

I have two sheets, sheet 2 is like

and sheet 3 is like:

and I am performing Vlookup function in sheet 3, for that i have taken all the ID's from sheet2 in a list using Data validation option. Now I need to select the value from dropdown and Salary of that ID should be the output. It's working fine in single sheet but in the case of two sheets, It shows error. I have written this:

=VLOOKUP(A13,'Sheet2:Sheet3'!A1:B6,4,FALSE)

Here A13 is the column in which I have defined that dropdown with all the ID'S. But it is showing this error:

A value used in this formula is of wrong data type.

I am unable to understand the problem.Thanks in advance.

Answer 1

Pass processing to a second VLOOKUP function using the value returned from the first VLOOKUP.

=VLOOKUP(VLOOKUP(A13,'Sheet2'!A:B, 2, FALSE), 'Sheet3'!A:B, 2, FALSE)

You were asking for a return from the 4^th column from A:B. I've changed that to 2 to get the value from Sheet3!B:B.

Answer 2

The error is due to the table definition ('Sheet2:Sheet3'...)

I think you've probably tapped on the sheet tabs whilst entering the table range for the table lookup. Your formula should look more like

=VLOOKUP(A13, 'Sheet3'!A1:B6, 2, False)

Assuming you want the value in column B on sheet 3 to be shown when referring to the value in Column A.

An alternative is to "define" the lookup table and then use that as the table reference. One of the easiest ways to do this is to select your whole table (Sheet3, Column A1 to B6 or however far it actually goes) then typing a name in the box on the top left of the screen that usually displays the name of the Cell that you are in (e.g. B9). See screenshot....

If you named that, say, "Salaries" then your new formula would be:

=VLOOKUP(A13, Salaries, 2, False)

Hope that helps...