IF statement with vlookup nested

**libertine86** · 08-16-2012, 11:05 AM

Hi,

I was hoping someone might be able to help me with a problem I've been having. I've attached a sample of the issue, as its work related i cant put the exact example but the principle is there.

On the sample table E1:F5 I have threshold values for each code. In column A I have codes, in column B I have values of these codes. I need column C to say either "Check" or "ok" depending on if the value of the code from Column A goes over the threshold from the table E2:F5.

I have tried to create a unique code to be greater then the unique code I've creating in column G

=IF((A2&B2)>(VLOOKUP(A2,$E$2:$G$5,3,0)),"Check","ok")

It appears that it is only picking up the first number on the lookup however because when the value goes beyond 2 digits it does not work correctly.

If anyone has any ideas could they help us out.

Thanks,

Lib

**NBVC** · 08-16-2012, 11:11 AM

You mean?

=IF(B2>VLOOKUP(A2,$E$2:$F$5,2,0),"check","ok")

copied down

**ChemistB** · 08-16-2012, 11:13 AM

Try this

=IF(VLOOKUP(A2,$E$2:$F$5,2,FALSE)<=B2,"OK","Check")
You don't need to combine columns as the item you are looking up is unique in the table.
Does that work for you?

**ben_hensel** · 08-16-2012, 11:17 AM

I don't understand where your problem is showing up exactly, because you're comparing two text strings together; I dunno how excel handles that but I don't think it's correct.

Anyway my advice is get rid of the text string comparison and just compare numbers.

C2 =IF(B2 > VLOOKUP(A2,$E$2:$F$5,2),"Check", "OK")

And you can delete column G4.

EDIT:
"Great minds think alike", huh...?

**ChemistB** · 08-16-2012, 01:05 PM

NBVC is the ultimate Excel Ninja.

**libertine86** · 08-16-2012, 02:49 PM

Thanks for that!

I was over complicating it with the combined columns I think, the idea was to make the combined value greater then the combined value on the lookup.

Still learning some of these things.

Thanks again,

Lib