LookUp returns #N/A for no reason

[CyberTaz]

Hi All -

I put together a small sheet to compare some prices. Names of 5 vendors are
in B3:F3 & prices for each the 8 products are in those respective columns.

In Column H (on each of the price rows) there is a Min fx that looks to the
five prices on that row & returns the lowest price for the item on that row
- no problem so far.

In Column I there is a Lookup fx using the Vector method to look at the Min
price in that same row of Column H, find that cell in the prices on the row
& return the name of the vendor (Row 3 of that same column containing the
lowest price). This works fine for 6 of the 8 products, but 2 of the lookups
are returning the #N/A error & I can't figure out why.

The other 6 rows were set up the same way & the Lookup fx was copied from
cell to cell using abs refs for the result vector. I've checked & reset the
formatting, rewritten the formula manually, re-entered the data, tried the
error tracing features & everything I can think of with no insights as yet.
I've also tried it in 4 different versions of XL (PC & Mac) with the same
results. Even the Evaluate feature in PC XL show that the lookup value is
fine but indicates that "the next evaluation will result in an error" but
won't tell me *why*. The lookup fx is simply:

=LOOKUP(H30,B30:F30,$B$3:$F$3)

Where am I going wrong?

TIA |:>)
Bob Jones
[MVP] Office:Mac

 

[Art]

Hi All -

I put together a small sheet to compare some prices. Names of 5 vendors are
in B3:F3 & prices for each the 8 products are in those respective columns.

In Column H (on each of the price rows) there is a Min fx that looks to the
five prices on that row & returns the lowest price for the item on that row
- no problem so far.

In Column I there is a Lookup fx using the Vector method to look at the Min
price in that same row of Column H, find that cell in the prices on the row
& return the name of the vendor (Row 3 of that same column containing the
lowest price). This works fine for 6 of the 8 products, but 2 of the lookups
are returning the #N/A error & I can't figure out why.

The other 6 rows were set up the same way & the Lookup fx was copied from
cell to cell using abs refs for the result vector. I've checked & reset the
formatting, rewritten the formula manually, re-entered the data, tried the
error tracing features & everything I can think of with no insights as yet.
I've also tried it in 4 different versions of XL (PC & Mac) with the same
results. Even the Evaluate feature in PC XL show that the lookup value is
fine but indicates that "the next evaluation will result in an error" but
won't tell me *why*. The lookup fx is simply:

=LOOKUP(H30,B30:F30,$B$3:$F$3)

Where am I going wrong?

TIA |:>)
Bob Jones
[MVP] Office:Mac

Bob,

From your description, it would appear that the minimum value can be
anywhere within a row.

LOOKUP, HLOOKUP, VLOOKUP all require that the entries in lookup_vector
are in ascending order. If not, your MIN result from column H may be
less than the first (lowest) entry in the lookup_vector and a #N/A would
result.

Perhaps the MATCH function with a match_type of 0 would be a better
choice as the lookup_array can be in any order. Couple the positional
result (minus 1) with an OFFSET function from the start of the vendor
list $b$3 to get the vendor name.

Something like this for the column I:

=offset($b$3,0,match(h30,b30:f30,0)-1))

HTH...

Art

 

[JE McGimpsey]

Hi All -

I put together a small sheet to compare some prices. Names of 5 vendors are
in B3:F3 & prices for each the 8 products are in those respective columns.

In Column H (on each of the price rows) there is a Min fx that looks to the
five prices on that row & returns the lowest price for the item on that row
- no problem so far.

In Column I there is a Lookup fx using the Vector method to look at the Min
price in that same row of Column H, find that cell in the prices on the row
& return the name of the vendor (Row 3 of that same column containing the
lowest price). This works fine for 6 of the 8 products, but 2 of the lookups
are returning the #N/A error & I can't figure out why.

The other 6 rows were set up the same way & the Lookup fx was copied from
cell to cell using abs refs for the result vector. I've checked & reset the
formatting, rewritten the formula manually, re-entered the data, tried the
error tracing features & everything I can think of with no insights as yet.
I've also tried it in 4 different versions of XL (PC & Mac) with the same
results. Even the Evaluate feature in PC XL show that the lookup value is
fine but indicates that "the next evaluation will result in an error" but
won't tell me *why*. The lookup fx is simply:

=LOOKUP(H30,B30:F30,$B$3:$F$3)

Where am I going wrong?

As Art said, Lookup expects a sorted list. Try

=INDEX($B$3:$F$3,MATCH(H30,B30:F30,0))

 

[CyberTaz]

As Art said, Lookup expects a sorted list. Try

=INDEX($B$3:$F$3,MATCH(H30,B30:F30,0))

Hi John & Art -

The asc. Sort crossed my mind but since there was no argument for
range_lookup (true/false) I thought I'd give it a shot anyway. Although what
you say makes sense & I am not questioning you, I think what really threw me
was that it works for 6 of the 8 rows - the content of which are as randomly
arranged as the content in the cells of the other 2 rows that render the
#N/A. Why would those 6 rows work & the other 2 not?

Thanks for the responses!

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[Art]

[...]

Hi John & Art -

The asc. Sort crossed my mind but since there was no argument for
range_lookup (true/false) I thought I'd give it a shot anyway. Although what
you say makes sense & I am not questioning you, I think what really threw me
was that it works for 6 of the 8 rows - the content of which are as randomly
arranged as the content in the cells of the other 2 rows that render the
#N/A. Why would those 6 rows work & the other 2 not?

Thanks for the responses!

Bob,
The notes on LOOKUP and its' variants indicate:

"Important The values must be placed in ascending order: ...,-2, -1,
0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the
correct value. Uppercase and lowercase text are equivalent."

My old Excel4 Function Reference book had some handwritten notes in it
that this would not only include the possibility of returning the wrong
value from the vector/array, but also an #N/A. Never could figure out
the exact algorithm.

MATCH is a better choice for the data sets that you have. It does have a
couple of "gotchas":

a) match_type 0 expects an exact match. I got bitten once by differences
in precision between the lookup_value and the lookup_array.

b) MATCH interprets the format types of lookup_value and the
lookup_array literally (no conversions). Thus, a numeric value which is
formatted as text will not match its' numerically formatted equivalent.
I got bit by this one also :-(.

Art

 

[CyberTaz]

Hi Art -

Yeah [like everybody else] I reviewed the help on Lookup & its kin *after*
the fact I'm quite familiar with H/V but haven't really had need for the
elder version. I may have been assuming that since I was using the vector
variant that it would scan the vector the way MIN & MAX do. Even so - having
reviewed Help along with the input here - I would not expect it to work
properly for any occurrence, let alone the majority - IOW, had I gotten #NAs
for every row the problem would have been more obvious.

At any rate I've tried both your solution as well as John's & they each
provide the desired effect. I find John's to be more "intuitive" [if you'll
pardon the term] but yours is more "interesting". I need to play around
with that one a bit as I haven't used OFFSET before.

Thanks again for the replies!

Regards |:>)
Bob Jones
[MVP] Office:Mac

[...]

Hi John & Art -

The asc. Sort crossed my mind but since there was no argument for
range_lookup (true/false) I thought I'd give it a shot anyway. Although what
you say makes sense & I am not questioning you, I think what really threw me
was that it works for 6 of the 8 rows - the content of which are as randomly
arranged as the content in the cells of the other 2 rows that render the
#N/A. Why would those 6 rows work & the other 2 not?

Thanks for the responses!

Bob,
The notes on LOOKUP and its' variants indicate:

"Important The values must be placed in ascending order: ...,-2, -1,
0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the
correct value. Uppercase and lowercase text are equivalent."

My old Excel4 Function Reference book had some handwritten notes in it
that this would not only include the possibility of returning the wrong
value from the vector/array, but also an #N/A. Never could figure out
the exact algorithm.

MATCH is a better choice for the data sets that you have. It does have a
couple of "gotchas":

a) match_type 0 expects an exact match. I got bitten once by differences
in precision between the lookup_value and the lookup_array.

b) MATCH interprets the format types of lookup_value and the
lookup_array literally (no conversions). Thus, a numeric value which is
formatted as text will not match its' numerically formatted equivalent.
I got bit by this one also :-(.

Art

 

[Art]

Hi Art -

Yeah [like everybody else] I reviewed the help on Lookup & its kin *after*
the fact I'm quite familiar with H/V but haven't really had need for the
elder version. I may have been assuming that since I was using the vector
variant that it would scan the vector the way MIN & MAX do. Even so - having
reviewed Help along with the input here - I would not expect it to work
properly for any occurrence, let alone the majority - IOW, had I gotten #NAs
for every row the problem would have been more obvious.

At any rate I've tried both your solution as well as John's & they each
provide the desired effect. I find John's to be more "intuitive" [if you'll
pardon the term] but yours is more "interesting". I need to play around
with that one a bit as I haven't used OFFSET before.

Thanks again for the replies!

Regards |:>)
Bob Jones
[MVP] Office:Mac

[...]
Bob,
Been there many times . Unfortunately, the O/L help often trims out
useful examples, helpful hints and pointers to other references. That's
why I guard my moth-eaten Excel4 reference books with my hen scratchings
like gold.

wrt to the two alternatives (mine and Johns'), certainly both will work.

One subtle difference is that with the INDEX function, the array syntax
format #2 (the one that returns a value) requires that the row and
column indices both be within the array specification, otherwise a #REF!
error will occur. Certainly not an issue when encoding the first time.
However, if you expand your tables to more vendor columns and absolute
references had been specified, the array specification may need to be
modified for every row.

Using the OFFSET function, the formulae can remain unchanged as only the
first cell in your vendor name list needs to be specified.

As I mentioned, the OFFSET function does require an adjustment of -1
from the MATCH result. The INDEX approach doesn't.

Not sure why Excel didn't use a consistent approach to indexing and
offsets, but that horse left the barn almost 20 years ago . I got
bite marks from these "differences" more than once .

Art

 

[JE McGimpsey]

One subtle difference is that with the INDEX function, the array syntax
format #2 (the one that returns a value) requires that the row and
column indices both be within the array specification, otherwise a #REF!
error will occur. Certainly not an issue when encoding the first time.
However, if you expand your tables to more vendor columns and absolute
references had been specified, the array specification may need to be
modified for every row.

Using the OFFSET function, the formulae can remain unchanged as only the
first cell in your vendor name list needs to be specified.

As I mentioned, the OFFSET function does require an adjustment of -1
from the MATCH result. The INDEX approach doesn't.

Another difference is that OFFSET is volatile while INDEX is not.

For most applications that does not make a lot of difference, but if you
have a lot of these functions, INDEX will be more efficient.

A side effect of OFFSET()'s volatility is that simply opening a file
which uses OFFSET will mark the workbook as "dirty", leading to a "do
you want to save" message when you close the workbook, even if you did
nothing to change any values or calculation results.

 

[Art]

Another difference is that OFFSET is volatile while INDEX is not.

For most applications that does not make a lot of difference, but if you
have a lot of these functions, INDEX will be more efficient.

A side effect of OFFSET()'s volatility is that simply opening a file
which uses OFFSET will mark the workbook as "dirty", leading to a "do
you want to save" message when you close the workbook, even if you did
nothing to change any values or calculation results.

John,
Interesting. I think you just answered an age-old question in an old
XLM4 macro program that I have. Any others come to mind that mark the
sheet as modified ?

Art

 

[JE McGimpsey]

Interesting. I think you just answered an age-old question in an old
XLM4 macro program that I have. Any others come to mind that mark the
sheet as modified ?

From the MS KnowledgeBase:

http://support.microsoft.com/kb/274500/en-us

CELL() OFFSET() TODAY()
INDIRECT() NOW()
INFO() RAND()

However, this article, applicable to WinXL2000:

http://support.microsoft.com/kb/248179/en-us

lists these functions:

AREAS()
INDEX()
OFFSET()
CELL()
INDIRECT()
ROWS()
COLUMNS()
NOW()
TODAY()
RAND()


I know that INDEX() *used* to be volatile in pre-MacXL04 and -WinXL02
versions, but is no longer so. By test, neither are COLUMNS(), ROWS()
and AREAS() in XL04.

 

[Art]

Interesting. I think you just answered an age-old question in an old
XLM4 macro program that I have. Any others come to mind that mark the
sheet as modified ?

From the MS KnowledgeBase:
[...]

Thanks, John. Some like INFO(), NOW(), DATE(), RAND() are obvious. Some
of the others are a bit surprising, but good to know about in any event.

Art