# Nesting IF and HLookup

September 29, 2010 at 15:21:06
Specs: Windows XP
 I need help with this.I have a spreadsheet that uses the followingColumn B19 value IIIAI want to look at the followingBegins at Row 5, Column IIA IB IC IIA IIB IIIA UNACCEPTABLEID IIC IIIB IIIC IID UNDESIREABLE IID IVA IVB ACCEPTABLE REVIEWIVC IVD ACCEPTABLE If B19 contains value of either IB,IC,IIA,IIB,IIIA return UNACCEPTABLEIf B19 has a value of either ID,IIC,IIB,IIC,IID then return UDESIRABLEetc.I can't nest beyond 7. When I use the following statement I receive UNACCEPTABLE as long as the values fall into IA,IB,IC,IIA,IIB,IIIA. But I receive #N/A for any other value in the other rows. The syntax being used is as follows.=IF(HLOOKUP(B19,\$I\$5:\$N\$5,1,FALSE)=B19,\$O\$5,IF(HLOOKUP(B19,\$I\$6:\$M\$6,2,FALSE)=B19,\$O\$6,IF(HLOOKUP(B19,\$I\$7:\$K\$7,1,FALSE)=B19,\$O\$7,IF(HLOOKUP(B19,\$I\$8:\$J\$8,1,FALSE)=B19,\$O\$8,"C"))))What am I missing. Getting a headache.

See More: Nesting IF and HLookup

#1
September 29, 2010 at 17:02:41
 If you use "IF(OR())" you only need 3 Nested IF's. I'm not going to type in every term, so here's a shortened version of what you could put together using IF and OR:=IF(OR(B19=I5, B19=J5, B19=K5), "Unacceptable", IF(OR(B19=I6, B19=J6), "Undesirable",IF(OR(etc.))However, that's pretty much brute force and not very elegant. This is much better:=IF(ISNA(MATCH(B19,I5:N5,0)), IF(ISNA(MATCH(B19,I6:M6,0)), IF(ISNA(MATCH(B19,I7:K7,0)), "Acceptable", "Acceptable Review"), "Undesirable"), "Unacceptable")Note: This will return Acceptable for IVC and IVD as well as any other value not found in your table. That's because it never really checks for IVC or IVD, it simply "defaults" to Acceptable if the value in B19 is not found in Row 5 6 or 7 of your table. If there is a chance that a "stray" value may end up in B19, then you need to add one more IF(ISNA(MATCH))) for Row 8 and supply a "Bad Value" or something like that to handle the stray values.

Report •

#2
October 4, 2010 at 08:08:56
 Thanks a lot. Worked flawlessly.

Report •

Related Solutions