Hlookup with multiple criteria.

[drewjwats]

I searched online for a good amount of time and came up empty handed, I may be explaining what I am attempting to do wrong.


I am trying to return a row value based on the customer number and a "Yes" in certain columns.

For example, in the following I would be attempting to return the value of the most recent yes. Week 2, for 0001 and week 5 for 0002. I cant for the life of me figure out what the formula would be.


Cus Number Week 1 Week 2 Week 3 Week 4 Week 5
0001 No Yes No No No
0002 No Yes No Yes Yes


Any help on this would be much appreicated.


Thank you!

 

[drewjwats]

To be more clear, I would be linking from a different spread sheet, which only has the customer number, I would be attempting to return the week of the most recent yes.

 

[Andrew Poulsom]

Does this help you (formula in I2 copied down)?

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Cus Number	Week 1	Week 2	Week 3	Week 4	Week 5		Cus Number	Week No
2	0001	No	Yes	No	No	No		0001	Week 2
3	0002	No	Yes	No	Yes	Yes		0002	Week 5
Sheet1

Cell Formulas
Range	Formula
I2	=LOOKUP(2,1/(INDEX(B$2:F$3,MATCH(H2,A$2:A$3,FALSE),0)="Yes"),B$1:F$1)

 

[drewjwats]

It worked! thanks so much!