Thread: a VLOOKUP quirk
View Single Post
  #5   Report Post  
Clarence Crow
 
Posts: n/a
Default

On Mon, 29 Nov 2004 08:31:51 -0500, Debra Dalgleish
advised:

Sorry, I misread your question. To prevent data being returned when the
code isn't an exact match with an entry in the table, you could use
INDEX/MATCH, instead of VLOOKUP. For example:

=INDEX($B$2:$B$13,MATCH(TRUE,EXACT(D3,$A$2:$A$13), 0))

entered as an array formula (Ctrl+Shift+Enter)

In this example, the codes are in A2:A13, the number to return are in
B2:B13, and the lookup value is in cell D3.

If the table contains 200x16fl, an entry of 200*16fl in cell D3 will
return #N/A, as will 200X16fl (X is not an exact match to x).

Or, instead of letting the user type a value to match, you could use
data validation to provide a dropdown list of values from the lookup table.

Thanks for that. I'll rewrite it with the new function and drag it
down the 7,000 odd rows.
We find it more expedient to type it in, thanks all the same.
(I actually had something going in Lotus 2.01 DOS whereby you could
pick with a Macro but some of the other guys were happy to type it
in).
It's just this Boss actually did some work when we were short-staffed
and then complained re the anomaly.
Now I can fix his wagon :)


-- "if you can see it coming, head it off at the pass, else put the wagons in a circle"
-- Please reply to this ng as:
-- my email adress is 100% faked to prevent proliferation of SPAM!!
-- Regards

-- Clarence Crow