Using Vlookup function in different worksheets

Aqib Maqbool

New Member
Joined
Apr 3, 2018
Messages
1
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
Joined
Aug 1, 2012
Messages
134
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))
 
Upvote 0

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
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),""))


ABCABCABC
1invoice IDData01Sheet1invoice IDData01Sheet1invoice IDData01Sheet
2C60006Data01C6sales data2r10001Data01R1rent2C10001Data01C1meals and entertainment
3C40004Data01C43r20002Data01R23C20002Data01C2
4C50005Data01C54r30003Data01R34C30003Data01C3
5C20002Data01C25r40004Data01R45C40004Data01C4
6C10001Data01C16r50005Data01R56C50005Data01C5
7r60006Data01R67r60006Data01R67C60006Data01C6
8r30003Data01R38r70007Data01R78C70007Data01C7
9C30003Data01C399C80008Data01C8
10C70007Data01C710C90009Data01C9
11C80008Data01C811
12C90009Data01C9
13r10001Data01R1
14r20002Data01R2
15r40004Data01R4
16r70007Data01R7
17r50005Data01R5
18
************************************************************************************************************

<tbody>
</tbody>


Markmzz
 
Last edited:
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top