Vlookup Multiple

[taylo5dc]

I have a very large database of numbers that have two IDs. I pulled the coding system (Table 2) that has ID1 and ID2 and the universal ID. I would like to be able to look up the first two IDs (table 1) and have the result be the universal ID. I believe the best way to do this would be Vlookup, but I have been unsucessful. Thank you in advance for your help.

	Table 1			Table 2
	ID1	ID2		ID1	ID2	universal ID
	50	10		30	40	117
				50	10	190
				40	70	200

<TBODY>
</TBODY>

 

[ADeYoung]

Usethe SUMSMIFS function

=SUMIFS(F2:F4,D2:D4,A2,E2:E4,B2) where F2:F4 = Universal ID Range, D2:D4 = ID1 Range (Table2), A2 = ID1(Table1), E2:E4 = ID2 Range(Table2) & B2 = ID2(Table1)

 

[Robert Mika]

Excel 2010

	A	B	C	D	E	F	G	H	I
1	Table 1						Table 2
2		ID1	ID2				ID1	ID2	universal ID
3		50	10	190			30	40	117
4							50	10	190
5							40	70	200

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula D3 =IF(ISNA(MATCH(TRUE,INDEX($G$3:$G$5&$H$3:$H$5=B3&C3,),0)),"",INDEX($I$3:$I$5,MATCH(TRUE,INDEX($G$3:$G$5&$H$3:$H$5=B3&C3,),0))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Aladin Akyurek]

I have a very large database of numbers that have two IDs. I pulled the coding system (Table 2) that has ID1 and ID2 and the universal ID. I would like to be able to look up the first two IDs (table 1) and have the result be the universal ID. I believe the best way to do this would be Vlookup, but I have been unsucessful. Thank you in advance for your help.

	Table 1			Table 2
	ID1	ID2		ID1	ID2	universal ID
	50	10		30	40	117
				50	10	190
				40	70	200

<tbody>
</tbody>

Control+shift+enter, not just enter:

=INDEX(UniversalIDrange,MATCH(ID1,IF(ID2range=ID2,ID1range),0))

 

[Robert Mika]

Control+shift+enter, not just enter:

=INDEX(UniversalIDrange,MATCH(ID1,IF(ID2range=ID2,ID1range),0))

Hi Aladin,
Is this faster that my non array approach?

 

[Aladin Akyurek]

Hi Aladin,
Is this faster that my non array approach?

Hi Robert

You mean of course non-CSE for indexifying an array result still means an array-processing formula. And I don't believe such is any faster than its CSE-counterpart. Maybe even the reverse is true.

 

[Robert Mika]

Hi Robert

You mean of course non-CSE for indexifying an array result still means an array-processing formula. And I don't believe such is any faster than its CSE-counterpart. Maybe even the reverse is true.

As always thank you for explanation and yes I meant CSE formula or in fact my "non CSE approach"