Lookup doesn't return what I want; alternative?

  • Thread starter Thread starter Paul Martin
  • Start date Start date
P

Paul Martin

Hi all

I have a list of values in a column and want to return the cell that
has the closest value greater than a given criteria. So if the
criteria is "Mad", and my list is:

Macz
Mada
Madb
Madc

Then I want to return "Mada" whereas VLOOKUP returns "Macz". Any
suggestions appreciated.

Thanks in advance

Paul Martin
Melbourne, Australia
 
And, to clarify, basically I'd like to do an INDEX-MATCH, but the
match isn't exact and the list isn't sorted.
 
Paul, I changed your criteria to MAD* and it worked
I used:
=VLOOKUP(F7,D7:D10,1,FALSE)
Where F7 = MAD*
D7:D10 is your list
 
Hi Paul

I suspect you are not telling us the full story here - like in your previous
posting to which Biff and I responded.
From what you finally told us there, the data is copied from a PDF file.

The items you are searching for are not consistent e.g Total North is not
Total North, but Total fcig so there is no easy method to determine what is
the start and end of your data.
In the last posting you made, it was not clear whether column A (a range you
were calling Alldata) contained Apples 167, or whether it contained Apples,
and column B held the value 197.

My suggestion to you would be to invest in a copy of Able2Extract.
I have found it to be excellent in taking data from a PDF directly into
Excel in a workable form.
You will find details here
http://www.investintech.com/prod_downloadsa2e.htm

--
Regards
Roger Govier

Paul Martin said:
And, to clarify, basically I'd like to do an INDEX-MATCH, but the
match isn't exact and the list isn't sorted.

__________ Information from ESET Smart Security, version of virus
signature database 4810 (20100127) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4810 (20100127) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Thanks for the responses. The solution that worked for me was
appending the lookup value with a "*" using INDEX-MATCH, ie:

INDEX(List, MATCH(A2 & "*", List, 0))

Paul
 
Back
Top