View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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
---