Multiple criteria lookup to return values in a column

**ydj** · 11-02-2010, 08:28 AM

I have a table like this : (the excel file attached: YDJ.xlsx)

Brand Size #ID Date Row Production No.
GY 1100 ME4752 2-Oct 1 HB 072
BS 900 E2E1051 2-Oct 1 HB 073
HK 825 L1E1488 2-Oct 2 HB 074
BS 750 A3E2081 2-Oct 2 HB 075
BS 900 53E1978 2-Oct 1 HB 076
GY 1000 E1E0519 2-Oct 2 HB 077
BS 1000 M1E0193 2-Oct 2 HB 078
GT 700 E1E0563 2-Oct 2 HB 079
GY 1100 09T111049 3-Oct 2 HB 080
GT 900 609S86-12 3-Oct 2 HB 081
GT 1000 209S58-29 3-Oct 1 HB 082
GT 900 609S89-28 3-Oct 2 HB 083
GT 750 709S58-26 3-Oct 1 HB 084
GT 900 209S58-26 3-Oct 1 HB 085
BS 900 B2E2384 3-Oct 1 HB 086
BS 1000 R3F0790 3-Oct 2 HB 087

I need excel to show all the #ID (in a column on other sheet) that match the Date : 2-Oct and Row : 2 for example, so the result will be like this :
#ID
L1E1488
A3E2081
E1E0519
M1E0193
E1E0563
#NUM!

What formula to do that?

Thank you very much guys! Any answer will be much appreciated.

**NBVC** · 11-02-2010, 08:59 AM

In G2 enter formula:

=IF(AND(D2=$H$1,E2=$I$1),COUNT(G$1:G1)+1,"")

copied down... this is a helper column to identify matches.

in H2 enter: =MAX(G:G) this finds number of matching records.

in H3:

=IF(ROWS($A$1:$A1)>$H$2,"",INDEX(C:C,MATCH(ROWS($A$1:$A1),$G:$G)))

copied down as far as you want. This extracts the IDs for matching records.

**ydj** · 11-03-2010, 12:18 AM

@ NBVC : Awesome, it works very good! Thank you very much for your help I really appreciate it, but with all respect, is there any better way to perform the action without adding a helper column to identify matches and the "=MAX(G:G)" cell? (I mean with one single formula)

**DonkeyOte** · 11-03-2010, 04:05 AM

Define "better" ?

The approach you've been given by NBVC is the best option available to you in terms of performance ... single cell formulae are hugely inefficient in comparison.

It would of course be simpler still to just use a Pivot Table... "Date" & "Row" as Report Filters and "ID Number" as Row Label.