Using Vlookup function in different worksheets

[Aqib Maqbool]

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...

 

[t0azt]

Something like that where A1 is your Invoice ID. Just replace tables and columns in vlookup as needed.

=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]

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