# Using Vlookup function in different worksheets

#### Aqib Maqbool

##### New Member
Hi! I am facing problem in using vlooup function on multiple tables. Basically I want to retrieve data from multiple tables using vlooup function on invoice ID. How to make it possible. If you give me formula then I will do it easily. I don't want to do it using macros. I want to retrieve data in (sales data worksheet) from rent worksheet and meals and entertainment worksheet using invoice id. invoice id for meals and entertainment has code C1,C2,C3.... and Invoice id for rent expense starting from r1, r2,r3...

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### t0azt

##### Board Regular
Something like that where A1 is your Invoice ID. Just replace tables and columns in vlookup as needed.
Code:
``=IF(LEFT(A1,1)="C",VLOOKUP(A1,'M&E'!\$A\$1:\$B\$4,2,0),IF(LEFT(A1,1)="R",VLOOKUP(A1,Rent!\$A\$1:\$B\$3,2,0),0))``

#### markmzz

##### MrExcel MVP
Hi! I am facing problem in using vlooup function on multiple tables. Basically I want to retrieve data from multiple tables using vlooup function on invoice ID. How to make it possible. If you give me formula then I will do it easily. I don't want to do it using macros. I want to retrieve data in (sales data worksheet) from rent worksheet and meals and entertainment worksheet using invoice id. invoice id for meals and entertainment has code C1,C2,C3.... and Invoice id for rent expense starting from r1, r2,r3...

Try this too:

In B2 of the sheet sales data and copy down

=IFERROR(VLOOKUP(A2,CHOOSE(1+(LEFT(A2)="C"),rent!\$A\$2:\$B\$8,'meals and entertainment'!\$A\$1:\$B\$10),2,0),"")

Or

=IFERROR(VLOOKUP(A2,rent!\$A\$2:\$B\$8,2,0),IFERROR(VLOOKUP(A2,'meals and entertainment'!\$A\$1:\$B\$10,2,0),""))

 A B C A B C A B C 1 invoice ID Data01 Sheet 1 invoice ID Data01 Sheet 1 invoice ID Data01 Sheet 2 C60006 Data01C6 sales data 2 r10001 Data01R1 rent 2 C10001 Data01C1 meals and entertainment 3 C40004 Data01C4 3 r20002 Data01R2 3 C20002 Data01C2 4 C50005 Data01C5 4 r30003 Data01R3 4 C30003 Data01C3 5 C20002 Data01C2 5 r40004 Data01R4 5 C40004 Data01C4 6 C10001 Data01C1 6 r50005 Data01R5 6 C50005 Data01C5 7 r60006 Data01R6 7 r60006 Data01R6 7 C60006 Data01C6 8 r30003 Data01R3 8 r70007 Data01R7 8 C70007 Data01C7 9 C30003 Data01C3 9 9 C80008 Data01C8 10 C70007 Data01C7 10 C90009 Data01C9 11 C80008 Data01C8 11 12 C90009 Data01C9 13 r10001 Data01R1 14 r20002 Data01R2 15 r40004 Data01R4 16 r70007 Data01R7 17 r50005 Data01R5 18 *** ********* ********* ********* ** *** ********* ********* ********* ** *** ********* ********* ***********************

<tbody>
</tbody>

Markmzz

Last edited:

Replies
1
Views
111
Replies
0
Views
288
Replies
3
Views
137
Replies
5
Views
173
Replies
2
Views
141

1,186,727
Messages
5,959,389
Members
438,419
Latest member
nayrbnalhguoc

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back