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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
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),""))


[TABLE="class: grid, width: 857"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]invoice ID[/TD]
[TD]Data01[/TD]
[TD]Sheet[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]invoice ID[/TD]
[TD]Data01[/TD]
[TD]Sheet[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]invoice ID[/TD]
[TD]Data01[/TD]
[TD]Sheet[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]C60006[/TD]
[TD]Data01C6[/TD]
[TD]sales data[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]r10001[/TD]
[TD]Data01R1[/TD]
[TD]rent[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]C10001[/TD]
[TD]Data01C1[/TD]
[TD]meals and entertainment[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]C40004[/TD]
[TD]Data01C4[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]r20002[/TD]
[TD]Data01R2[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]C20002[/TD]
[TD]Data01C2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C50005[/TD]
[TD]Data01C5[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]r30003[/TD]
[TD]Data01R3[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]C30003[/TD]
[TD]Data01C3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]C20002[/TD]
[TD]Data01C2[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]r40004[/TD]
[TD]Data01R4[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]C40004[/TD]
[TD]Data01C4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]C10001[/TD]
[TD]Data01C1[/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]r50005[/TD]
[TD]Data01R5[/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]C50005[/TD]
[TD]Data01C5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]r60006[/TD]
[TD]Data01R6[/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD]r60006[/TD]
[TD]Data01R6[/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD]C60006[/TD]
[TD]Data01C6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]r30003[/TD]
[TD]Data01R3[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]r70007[/TD]
[TD]Data01R7[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]C70007[/TD]
[TD]Data01C7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]C30003[/TD]
[TD]Data01C3[/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD]C80008[/TD]
[TD]Data01C8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]C70007[/TD]
[TD]Data01C7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]C90009[/TD]
[TD]Data01C9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]C80008[/TD]
[TD]Data01C8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]C90009[/TD]
[TD]Data01C9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]r10001[/TD]
[TD]Data01R1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]r20002[/TD]
[TD]Data01R2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]r40004[/TD]
[TD]Data01R4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]r70007[/TD]
[TD]Data01R7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]r50005[/TD]
[TD]Data01R5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]*********[/TD]
[TD]*********[/TD]
[TD]*********[/TD]
[TD]**[/TD]
[TD]***[/TD]
[TD]*********[/TD]
[TD]*********[/TD]
[TD]*********[/TD]
[TD]**[/TD]
[TD]***[/TD]
[TD]*********[/TD]
[TD]*********[/TD]
[TD]***********************[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,245
Messages
6,177,399
Members
452,774
Latest member
Macca1962

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
Back
Top