multiple Vlook up - (when data to be collected from different tables)

Akki20

New Member
Joined
May 9, 2014
Messages
13
Source data
May'14May'14Jun'14Jun'14
Room noQtyRoom noQty
L120L148
L221L249
L326L354
L488D149
L50D254
L632L4116
L742L528
L660
L770
Required Data
Room noMay'14Jun'14
L12048
L22149
L32654
L488116
L5028
L63260
L74270
D1 49
D2 54

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

jduley

Board Regular
Joined
May 8, 2014
Messages
64
I would just use 2 vlookups. For May use =vlookup(Room No, May Source Table, 2, False) and for June use =vlookup(Room No, June Source Table, 2, False).
 

Akki20

New Member
Joined
May 9, 2014
Messages
13
I would just use 2 vlookups. For May use =vlookup(Room No, May Source Table, 2, False) and for June use =vlookup(Room No, June Source Table, 2, False).
----

Hi Jduley

this will be a time consuming process to put separate vlook-up. I am searching for a single formula to get the required result.
 

Mike Szczesny

Active Member
Joined
Feb 7, 2008
Messages
411
Assume a simple data range. A1:B4 and D1:E4. Columns A and D contain letters. Columns B and E have numbers Use at A7:
=IFERROR(IFERROR(INDEX($A$1:$A$4,MATCH(0,COUNTIF($A$6:A6,$A$1:$A$4),0)),INDEX($D$1:$D$4,MATCH(0,COUNTIF($EW$6:EW6,$D$1:$D$4),0))),"")

<tbody>
</tbody>

Use Cntrl-Shift-Enter copy down. this will give the unique items. The outer iferror formula gives blanks for error messages. The inner iferror formula selects D1:E4.

B7: =vlookup(A7,$B$1:$B4,2,false)
C7: =iferror(vlookup(A7,$E$1:$E$4,2,false),"") Copy down. The ifferor formulas gives blanks for error messages
 

Watch MrExcel Video

Forum statistics

Threads
1,109,461
Messages
5,528,922
Members
409,847
Latest member
Foster034
Top