Using multiple sheet reference in vlookup function

[sherifpv]

Following my first question, I have one more problem to solve: Sales datas are stored in Sheet1 & Sheet2. In Sheet3 invoice is prepared and it will fill data while I click in any voucher number in the first column of sheet1. I want to get invoice details of sheet2 also into sheet3. How can I use variable name of sheet reference in vlookup formula? (=vlookup(a3,'[sheet]sheet1'!a2:j100,2)

 

[bjurney]

Following my first question, I have one more problem to solve: Sales datas are stored in Sheet1 & Sheet2. In Sheet3 invoice is prepared and it will fill data while I click in any voucher number in the first column of sheet1. I want to get invoice details of sheet2 also into sheet3. How can I use variable name of sheet reference in vlookup formula? (=vlookup(a3,'[sheet]sheet1'!a2:j100,2)

You would want to use some kind of if statement, Im not sure if I entirely understand where your values are, so you may need to modify but something like this should work,

=IF(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE)),VLOOKUP(A1,Sheet3!A:B,2,FALSE),VLOOKUP(A1,Sheet2!A:B,2,FALSE))

It basically checks if the vlookup erros when it looks on the first sheet and then checks the second sheet, if there is no error, it looks on the first sheet.

 

[Marc]

=VLOOKUP(A3,Sheet1!A2:J100,2) works for me.

You might also want to assign a name to the Sales data ranges in sheets one and two and use those names in your VLOOKUP, something like:

=VLOOKUP(A3,S1DATA,2,FALSE)

=VLOOKUP(A3,S2DATA,1,FALSE)

Where S1DATA is the range on sheet one say and S2DATA captures sheet two.