Excel - Lookup doesn't return what I want; alternative?
Asked By Paul Martin
26-Jan-10 11:50 PM
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
Excel
(1)
VLOOKUP
(1)
MATCH
(1)
INDEX
(1)
Database
(1)
PaulI
(1)
Alldata
(1)
WorkedI
(1)
Paul Martin replied to Paul Martin
And, to clarify, basically I'd like to do an INDEX-MATCH, but the
match is not exact and the list is not sorted.
whydoyoumakethingsoverlycomplicated replied to Paul Martin
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
Pete_UK replied to whydoyoumakethingsoverlycomplicated
But will you find Mada if the list is not sorted (as the OP states) ?
Pete
Roger Govier replied to Paul Martin
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
__________ Information from ESET Smart Security, version of virus signature database 4810 (20100127) __________
The message was checked by ESET Smart Security.
http://www.eset.com
Paul Martin replied to Roger Govier
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
ous
is
you
s,
tm
re database 4810 (20100127) __________
How to sum by month Excel I have a sales order spreadsheet that gets updated daily from our ERP system. The me how to do it with a formula. Thanks in advanced for your help!!! Charles Excel Worksheet Discussions Excel 2003 (1) SUMPRODUCT (1) Worksheet (1) Workbook (1) VLOOKUP (1) INDEX (1) MATCH (1) TEXT (1) This sounds like the perfect opportunity to learn about pivottables. Make sure a few links: Debra Dalgleish's pictures at Jon Peltier's site: http: / / peltiertech.com / Excel / Pivots / pivottables.htm And Debra's own site: http: / / www.contextures.com / xlPivot01.html John Walkenbach also has some at: http: / / j-walk.com / ss / excel / files / general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff
looking up a value only if it returns a value in another list Excel I have two data tables in Excel 2007. Table A holds a bill of materials with parent and child parts laid out suitable for use with vlookup. Table B holds a selected list of raw materials which are child parts. I want is outside my knowledge. Can anyone please point me in the right direction? Thanks - - Richard Excel New Users Discussions Microsoft Excel (1) Excel 2007 (1) ISNUMBER (1) Workbook (1) MATCH (1) INDEX (1) COUNT (1) Database (1) Can you post a small example and the expected result
Getting the next higer value with Vlookup - True Excel In VLookup / Hlookup function the True parameter gives the closest match with the search value but less than the search value. . . . i want to get the A B C 5 10 15 6 11 16 9 14 19 If i use = Vlookup(7, [Array], 2, 0) / = Vlookup(7, Array], 2, True) . . . . the answer is get is 11 BUT how to get it as 14!!!??? Please reply me at : getu32@gmail.com Excel Programming Discussions Excel (1) MATCH (1) INDEX (1) ISNA (1) Database (1) RegardsRoger (1) HiOne (1) Hlookup (1
Evaluating 3 Excel Sheets Excel Hi newsgroup I have a excel file which has 3 sheets. This 3 sheets are feeded with data of a database. In the first Sheet i have data from a table 'questions'. This table has a also the id of questions and candidate. Now I like to create another sheet in excel, which shows me the third sheet combined with sheet1 and sheet2. The relationship is given by the ids. Has anyone an idea how to do this? I think excel should have this functionality. Thanks for every help. best regards Ueli Werner Excel Discussions Sheets (1) Excel (1) Worksheet (1) FilterUR (1) Database (1) Do the id's show up in each
to CONCATENATE from the smallest date with at least 2 criteria Excel Dear All, Here is the sample database. Date Salesman Region 16-Aug-08 A N 16-Aug-09 B E 16-June the date in that arrays). thank you for your kind attention and help. respectfully, andri Excel Worksheet Discussions Microsoft Excel (1) Excel (1) CONCATENATE (1) SUBSTITUTE (1) SUMPRODUCT (1) Workbook (1) COUNTIF (1) LOOKUP (1) Andri, As by pressing CTRL+Shift+Enter and not just Enter. If you do it correctly then Excel will put curly brackets around the formula {}. You cannot type these yourself. If you edit strDelimiter is optional delimiter. Default is space Examples: '1. With data in A1:C11; to vlookup 'B' and "E" from 2 and 3rd columns and sort and concatenate each entry with Thus this could be re-used for all type of LOOKUP() CONCATENATE() requirements - - Jacob (MVP - Excel) Try this: = IF(ISERR(MATCH(SMALL(IF((Salesman = "B")*(Region = "E"), COUNTIF(Date, "<"&Date)), ROWS