Vlookup using cell with results of if statement

[gisawear]

=IF(IFERROR(FIND("800",D11),)>0,"800",IF(IFERROR(FIND("1000",D11),)>0,"1000",IF(IFERROR(FIND("1200",D11),)>0,"1250",IF(IFERROR(FIND("1600",D11),)>0,"1600",IF(IFERROR(FIND("2000",D11),)>0,"2000","#Value")))))

The calculation to the above is a value of 800 for a particular cell

I want to use this cell in a Vlookup as the lookup value and return NW08

Is there any reason why this won't work?

=Vlookup($C$4,$E$20:$E$31,2,False)

Number Code
800 NW08
1000 NW10
1250 NW12
1600 NW16
2000 NW20
2500 NW25
3200 NW32
4000 NW40
4500 NW40b
5000 NW50
6300 NW63

[Fotis1991]

One reason could be that Exell see the numbers in quotes, as TEXT.

IF(IFERROR(FIND("800",D11)......

Use

IF(IFERROR(FIND(800,D11)

[Richard Buttrey]

Hi,

The value your IF.. cell is returning is the text value "800" and not a numeric value 800. I suspect your Vlookup table has numbers in the first column rather than text.
Change either your table or your IF.. formula.

[gisawear]

Thanks guys that might be the case. I have tried changing both the formula and table to text and no luck.
Can you suggest an "IF,AND,OR" formula that would replace the IFERROR formula?


=IF(IFERROR(FIND("800",D11),)>0,"800",IF(IFERROR(FIND("1000",D11),)>0,"1000",IF(IFERROR(FIND("1200",D11),)>0,"1250",IF(IFERROR(FIND("1600",D11),)>0,"1600",IF(IFERROR(FIND("2000",D11),)>0,"2000","#Value")))))

[Fotis1991]

cAN YOU UPLOAD A SMALL SAMPLE WORKBOOK?

[jason.b75]

What are the possible entries in D11?

See if this works

=IFERROR(LOOKUP(2,1/FIND({800,1000,1200,1250,1600,2000},D11),{800,1000,1200,1250,1600,2000}),NA())