Need "TRUE" VLOOKUP to be more specific

There are ways to work out a partial match w/o using Vlookup. Can you post an example?


-Edit- just noticed the clock! it's quittin' time.


Here's an example using an array formula (see attachment). Instead of Vlookup, I need to make an array function return to me the first matching row:

{=MIN(IF(NOT(ISERROR(FIND(H$4,D:D))),ROW(D:D),""))}

See attachment. You can use something like that

Using TRUE works in most situations

That's because when you use TRUE, excel expects a sorted list - and when it cannot find a match, it will return the next lowest "match"

OK, try this...
=INDEX($J$6:$K$11,MATCH(LEFT(IF(C6="",C5,C6),FIND("/",IF(C6="",C5,C6))-1)&"*",$I$6:$I$11,0),MATCH(D6,$J$5:$K$5,0))
copied down

Try this (attached).

Please Login or Register  to view this content.

That works, but I also tried this:


=INDEX(L$6:L$11,MATCH($C6,$K$6:$K$11,1)+1)

That gives bad results when a match is not found - Your 'BLOOMBERG/GLOBAL/GP' line is not found in any of the names. Should that be 'BLOOMBERG FINANCIAL MARKETS/GLOBAL/GP'?

One suggestion that I have not seen suggested is the use of "wildcards" when the 4th argument is false. When the 4th argument is false, the lookup value can include wildcard characters. For example =VLOOKUP("Bloomberg*",$I$6:$K$11,2,FALSE) will find the result for the first entry in the table that starts with "Bloomberg". I tried something like =VLOOKUP(LEFT($C6,8)&"*",$I$6:$K$11,2,FALSE) (which assumes that the first 8 characters of the strings in column C will be sufficiently unique for accurate lookup), and it seemed to work correctly.