Using IFERROR with VLOOKUP

[Whylucky]

I am trying to find a way to pull info from a string and use that as a lookup_value for VLOOKUP. I cam up with the equation

=VLOOKUP(IFERROR(LEFT($C4,FIND("x",$C4,1)-2),"NA"),H4:I20,2,FALSE)

however it keeps returning an error. I know the VLOOKUP works and i know the IFERROR works, am i missing something when combining them? Any help will be greatly appreciated.

Regards,
Whylucky

 

[Gerald Higgins]

If the IFERROR part fails, it's going to feed "NA" to the VLOOKUP part.

Can your VLOOKUP cope with that ?

 

[Whylucky]

No the VLOOKUP wouldnt be able to handle the NA, however, as i stated ISERROR is correctly finding the right value but the VLOOKUP is still failing

 

[Aladin Akyurek]

=IFERROR(VLOOKUP(LEFT($C4,FIND("x",$C4,1)-2),H4:I20,2,0),"NA")

If this is not what you need, try to specify what you have in C4.

 

[Whylucky]

Yes that works better, but i still have the issue where the VLOOKUP can't process the number generated from the FIND function. Is there a reason or am i missing something on why this wont work?

 

[Aladin Akyurek]

Yes that works better, but i still have the issue where the VLOOKUP can't process the number generated from the FIND function. Is there a reason or am i missing something on why this wont work?

LEFT returns text even if its input is a number.

Coercing/converting with +0

LEFT($C4,FIND("x",$C4,1)-2)+0

would make the LEFT result a number.

 

[Whylucky]

that's perfect, thanks for the help and the explanation, i really appreciate it

 

[Aladin Akyurek]

that's perfect, thanks for the help and the explanation, i really appreciate it

You are welcome.