Difficult HLOOKUP/VLOOKUP

[edwardpestian]

I have a column of codes (A-Z): B5:B108. Every 4 rows contains the same
code. Column C5:C108 contains another code (1-26). Once again every 4
rows contains the same code. I need to say IF AC36 = Code 1, and IF
AD36 = Code 2, then lookup the two columns that meet these criteria,
and return the value in column CY on the corresponding 4th row.

Thanks in advance.

ep

 

[Guest]

I have a column of codes (A-Z): B5:B108. Every 4 rows contains the same
code. Column C5:C108 contains another code (1-26). Once again every 4
rows contains the same code. I need to say IF AC36 = Code 1, and IF
AD36 = Code 2, then lookup the two columns that meet these criteria,
and return the value in column CY on the corresponding 4th row.

Try array-entered* in say, AE36:
=INDEX($CY$5:$CY$108,MATCH(1,($B$5:$B$108=AC36)*($C$5:$C$108=AD36),0)+3)

*Press CTRL+SHIFT+ENTER to confirm the formula
(instead of just pressing ENTER)

As-is, formula can then be copied down to return correspondingly for other
pairs of values down cols AC and AD

---

 

[edwardpestian]

Perfecto Max!

thx

ep

 

[Guest]

Perfecto Max!
thx

Glad it worked, EP !

---