Looking for LOOKUP variation

[Salmon Egg]

I have not used the LOOKUP and MATCH functions very much. I would like a
function that returns an exact match and leaves a blank or error value
otherwise. In my case, I have a list of words in the lookup vector. I
have another list of words for which I want to find which individual
words are listed in the lookup vector.

The way I understand LOOKUP, the items in the lookup vector must be
ordered. Moreover, I get a vector element returned if the lookup word is
between two vector elements. That is what I am trying to avoid. I think
that I can devise something complicated that will do it for me, but it
would be nice to have something simple.

Bill

 

[DJH]

look at th eTrue /False argument in the lookup formula

 

[Steve]

|I have not used the LOOKUP and MATCH functions very much. I would
like a
| function that returns an exact match and leaves a blank or error
value
| otherwise. In my case, I have a list of words in the lookup vector.
I
| have another list of words for which I want to find which individual
| words are listed in the lookup vector.
|
| The way I understand LOOKUP, the items in the lookup vector must be
| ordered. Moreover, I get a vector element returned if the lookup
word is
| between two vector elements. That is what I am trying to avoid. I
think
| that I can devise something complicated that will do it for me, but
it
| would be nice to have something simple.
|
| Bill
|
| --
| Most people go to college to get their missing high school
education.



I like the VLOOKUP function best. The ONLY rule is that you have to
SORT the column that is used in the VLOOKUP in order to get the
correct output. If your lookup value isn't listed, then use the FALSE
to return "N/A" in the destination cell.

=VLOOKUP(original search term,array to search, column to return value
from,FALSE - if the value is not found)

I've never used the plain LOOKUP function.

Let us know if we've helped or confused you!
--
Steve Spence
Independent AMSOIL Dealer
AMSOIL - The "Once A Year" Oil Change
Unemployed Car Guy - Trying To Earn A Living
35 Years of G.M. Parts Experience
AutoCAD R14 Certified - 2D & 3D (ACIS solids)
URL: http://synthetic-oil-tech.com/1690163
Email: (e-mail address removed)

 

[DJH]

The false argument is used when the lookup array is not order and theVlookup
formula will return an exact match and if no match is found then it is
return N/A.

 

[Steve]

| The false argument is used when the lookup array is not order and
theVlookup
| formula will return an exact match and if no match is found then it
is
| return N/A.
|

I have a 6,000 line Excel spreadsheet that is compared to another
6,000 line spreadsheet to examine what changes between the two -
pricing and part numbers are the two fields which get examined and
compared.. If the column searched is not sorted, I get erroneous
results. If the searched column is sorted, I don't get those bad
results. Somehow, the Excel programming has a bug in it. I don't
always get bad data, but I don't want to look through 6,000 lines
manually to visually check that the results "look" correct.

I use VLOOKUP exclusively - partly because I understand how it works
and partly because I don't understand (completely) how the LOOKUP and
MATCH work together. It's just easier for me to sort both
spreadsheets and not have to worry about getting bad results.

I use the FALSE argument to signal when an item is present in the NEW
spreadsheet that is not present in the OLDER one. That way, I can
tell right away that the newer spreadsheet has new values in it.

I suppose because I understand what is happening - and it's exactly
what I need - I prefer VLOOKUP.

Maybe I need to take another look at LOOKUP, but what I remember from
researching it, the documentation at Microsoft online left large gaps
in doing a thorough explanation. And that's another reason I am more
comfortable with VLOOKUP - my pea sized brain was able to process the
explanation.

--
Steve Spence
Independent AMSOIL Dealer
AMSOIL - The "Once A Year" Oil Change
Unemployed Car Guy - Trying To Earn A Living
35 Years of G.M. Parts Experience
AutoCAD R14 Certified - 2D & 3D (ACIS solids)
URL: http://synthetic-oil-tech.com/1690163
Email: (e-mail address removed)

|

 

[Salmon Egg]

Thanks to DJH and Steve. I got what I needed using VLOOKUP. There is an
old joke about how Microsoft provides accurate although almost useless
information. It seems like there information on functions fit that bill.

Bill