Difficult HLOOKUP/VLOOKUP
 

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


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=558303

Difficult HLOOKUP/VLOOKUP
 

"edwardpestian" wrote:
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Difficult HLOOKUP/VLOOKUP
 

Perfecto Max!

thx

ep


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=558303

Difficult HLOOKUP/VLOOKUP
 

"edwardpestian" wrote:
Perfecto Max!
thx

Glad it worked, EP !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---