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 following

Column B19 value IIIA

I want to look at the following
Begins at Row 5, Column I
IA IB IC IIA IIB IIIA UNACCEPTABLE
ID IIC IIIB IIIC IID UNDESIREABLE
IID IVA IVB ACCEPTABLE REVIEW
IVC IVD ACCEPTABLE

If B19 contains value of either IB,IC,IIA,IIB,IIIA return UNACCEPTABLE
If B19 has a value of either ID,IIC,IIB,IIC,IID then return UDESIRABLE
etc.
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

Report •


#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


Ask Question