ISNA unable to work with IF(OR(... ?

[BC-GT]

Hi All,

I've given up trying to find why this doesn't work and just need to get it working!

I need to lookup prices for products I have codes for that may (or may not) appear in any one of a number of price-list sheets in various files.

I want to use the vlookup values if not zero and not the #N/A error and otherwise continue looking through the other sheets until I do get a legitimate value.

This seems to work except where ISNA is involved, is this a known issue and is there an answer to getting around it?

The gist of my formula is:
IF(OR(VLOOKUP(...sheet1...)=0,ISNA(...sheet1...),"This Text!",VLOOKUP(...sheet1...) *1.3)

where I should either be seeing a valid value or the text (this will be replaced by nested searches through the other sheets once I can solve this bit - unless someone has a tidier idea?) but I am still getting an #N/A returned.

If I try either part without the OR the text shows when it should. As soon as I try both with the OR it goes pear-shaped... (even where the simpler version returns a zero).

I had very little hair to being with but can someone save what little I have left as the only other option I can see is incredibly tedious and massively time-consuming?

Thanks!

 

[GraH - Guido]

shouldn't it be something like
IF(OR(VLOOKUP(...sheet1...)=0,ISNA(VLOOKUP(...sheet1...)),"This Text!",VLOOKUP(...sheet1...) *1.3)

But, as you propably know since this is your 4th post, a best practice is to upload a sample file...

 

[BC-GT]

Hi GraH,

Yes, the ISNA is on the VLOOKUP.

Unfortunately, being a work problem and accessing and listing sensitive information, I can't upload the specifics.

I was more wondering if it was a known issue with the ISNA seemingly not working in an IF(OR situation, with a work-around.

Thanks,

GT

 

[bosco_yip]

To ignore zero and error result, try to change your formula :

From this >>
=IF(OR(VLOOKUP(...sheet1...)=0,ISNA((VLOOKUP(...sheet1...)),"This Text!",VLOOKUP(...sheet1...) *1.3)

Into this >>
=IFERROR(1/(1/VLOOKUP(...sheet1...)*1.3),"This Text!")

Regards
Bosco

 

[Peter Bartholomew]

I have spend entirely to much time playing with alternative solutions!

What I finished with was to assume the codes are sorted ascending in each table and define named formulas 'found1?' etc. that refer to
= IFERROR( LOOKUP(code, Table1['code]) = code, FALSE )
Theses formulae, if evaluated, return TRUE or FALSE depending on whether the 'code' is found in the table. Each formula is evaluated in turn using a further named formula 'Table0'
= IFS( found1?, Table1, found2?, Table2, found3?, Table3 )

Once one knows the 'code' is definitely present within the selected table, as returned by the formula, then
= factor * LOOKUP( code, Table0 )
provides the result.