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

[Akki20]

Source data
May'14	May'14		Jun'14	Jun'14
Room no	Qty		Room no	Qty
L1	20		L1	48
L2	21		L2	49
L3	26		L3	54
L4	88		D1	49
L5	0		D2	54
L6	32		L4	116
L7	42		L5	28
			L6	60
			L7	70
Required Data
Room no	May'14	Jun'14
L1	20	48
L2	21	49
L3	26	54
L4	88	116
L5	0	28
L6	32	60
L7	42	70
D1		49
D2		54

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

 

[jduley]

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]

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]

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