Lookups (Excel 2003)

[D_N_L]

Hi all,

I have some data, workbook attached. My data is structured akin to A1:C7. I have two requirements I am trying to pull out but I have to use 2003

I am trying to pull out those names based on one criteria and then those names based on two criteria (examples shown). I know I could write an IF statement but I'm not sure how to present the returned values above one another.

Can anyone help please?

[AlKey]

With one criteria

=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$7="Yes",ROW($B$2:$B$7)),ROWS($A$1:A1))),"")

With two criteria

=IFERROR(INDEX(A:A,SMALL(IF($C$2:$C$7="Honda",IF($B$2:$B$7="Yes",ROW($C$2:$C$7))),ROWS($A$1:A1))),"")



...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E	F
1	Name	Result	Car	------	Yes	Yes + Honda
2	John	Yes	Ford		John	Bill
3	Dave	No	Renault		Bill	****
4	Bill	Yes	Honda		****
5	Bob	No	Honda
6	Tony	No	Ford
7	****	Yes	Honda

This array formula entered in E2:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF(B$2:B$7="Yes",ROW(B$2:B$7)),ROWS(E$2:E2)))))

Copy down until you get blanks.

This array formula** entered in F2:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF(B$2:B$7="Yes",IF(C$2:C$7="Honda",ROW(C$2:C$7))),ROWS(E$2:E2)))))

Copy down until you get blanks.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[D_N_L]

Hi both,

Fantastic. Tony I hope you don't mind I used AlKey's first as he replied sooner. Thank you. I added IF(ISERROR to your's Alkey and perfect. I need to remember that formula.

Thank you!

[Tony Valko]

Looks like the forum censor doesn't like the name D*I*C*K.

Thanks for the feedback!

[D_N_L]

@ tony. Sorted.

[AlKey]

Thanks for the feedback!

I forgot about Excel 2003

Here is the alternative

=IF(ROWS($B$1:B1)>COUNTIF($B$2:$B$7,"yes"),"",INDEX(A:A,SMALL(IF($B$2:$B$7="Yes",ROW($B$2:$B$7)),ROWS($A$1:A1))))

=IF(ROWS($B$1:B1)>COUNTIFS($C$2:$C$7,"Honda",$B$2:$B$7,"yes"),"",INDEX(A:A,SMALL(IF($B$2:$B$7="Yes",IF($C$2:$C$7="Honda",ROW($B$2:$B$7))),ROWS($A$1:A1))))

[D_N_L]

Thank you Alkey!!!

[Tony Valko]

The 2nd formula uses COUNTIFS. You'll have to use something else for Excel 2003, either SUMPRODUCT or SUM(IF.

[D_N_L]

Argh bummer. Had'nt gotten round to that one yet. Thank you for pointing that out Tony. Do I just replace the Count for Sum (i.e. direct replacement?)

[Tony Valko]

Replace:

COUNTIFS($C$2:$C$7,"Honda",$B$2:$B$7,"yes")

With:

SUMPRODUCT(--($C$2:$C$7="Honda"),--($B$2:$B$7="yes"))

However, why use this error trap:

=IF(ROWS($B$1:B1)>SUMPRODUCT(--($C$2:$C$7="Honda"),--($B$2:$B$7="yes")),"",

When this one will do:

=LOOKUP("zzzzz",CHOOSE({1,2},"",

[AlKey]

Here is with SUM

=IF(ROWS($B$1:B1)>SUM(--($B$2:$B$7="yes")),"",

=IF(ROWS($B$1:B1)>SUM(--($B$2:$B$7="yes")*($C$2:$C$7="Honda")),"",

[Tony Valko]

=IF(ROWS($B$1:B1)>SUM(--($B$2:$B$7="yes")*($C$2:$C$7="Honda")),"",

No need for the double unary.

SUM(($B$2:$B$7="yes")*($C$2:$C$7="Honda"))

[popipipo]

You can also use a pivot table