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),""))

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