Text string closest match lookup not using vlookup true

RyanF · #1 06-29-2009, 03:11 AM

Hi all

I am trying to find a formula to find closest match on a text string using index match preferably and vlookup true so the data does not need to be altered.

I know the formula using index match to match closest amounts is =INDEX($H$2:$K$60,MATCH(A2&C2,$H$2:$H$60&$J$2:$J$61,0),1) entered as an array.
What would be the equivalent to look up closest text match?

I've attached a worksheet were I would be trying to lookup the vehicle type on sheet one column D to closest match on sheet two column E.

Appreciate any help.

martindwilson · #2 07-01-2009, 09:12 AM

=INDEX($H$2:$K$60,MATCH(A2&C2,$H$2:$H$60&$J$2:$J$60,0),1) isnt an array entered formula.
it matches on 2 columns and returns from third, not a closest match either!
so what constitutes a close match in your examples?

RyanF · #3 07-01-2009, 12:14 PM

Sorry I entered the wrong formula-the formula I meant if you were looking up closest match on amount is INDEX($E$2:$E$248,MATCH(MIN(ABS($C$2:$C$247-C2)),ABS($C$2:$C$247-C2),0))

I'm looking for an equivalent for text string closest matching-as a Vlookup true would, but without altering the data source into ascending order.

In this case I would be looking for closest text match between column "d" in sheet one and column "e" in sheet two.

Welcome to the Excel Forum

Forum Statistics