Menu
I have used INDEX(MATCH()) formulas of this form before, but never with wildcards. Could anyone explain as two why adding in the A2&"*" would return an error value? I have checked the data and there definitely should be a match.
The formula is as below:
{=INDEX(I1:M1000,MATCH(1,(M1:M1000=Sheet3!B1)*(I1:I1000=A2&"*"),0),2)}
375
INDEX MATCH with multiple criteria enables you to do a successful lookup when there are multiple lookup value matches. In other words, you can look up and return values even if there are no unique values to look for.
To get the value of the first match in a range using a wildcard, you can use an INDEX and MATCH formula, configured for exact match.
Although Microsoft Excel provides special functions for vertical and horizontal lookup, expert users normally replace them with INDEX MATCH, which is superior to VLOOKUP and HLOOKUP in many ways. Among other things, it can look up two or more criteria in columns and rows.
Suppose A2 contains "abc".
You can put a wild card in the search string of a match statement e.g.
MATCH(A2&"*",I1:I1000,0)
to search for anything beginning with abc, but not in the range that you're searching.
Also, the bracket
(I1:I1000=A2&"*")
is just comparing each cell in the range I1:I1000 with A2&"*" so in this context it just does a literal match of each cell with "abc*" and the * doesn't work as a wildcard.
You could try using FIND or SEARCH to do a partial match or using LEFT to get the first few characters of the strings in I1:I1000
=INDEX(I1:M1000,MATCH(1,(M1:M1000=B1)*(FIND(A2,I1:I1000)=1),0),2)
=INDEX(I1:M1000,MATCH(1,(M1:M1000=B1)*(LEFT(I1:I1000,LEN(A2))=A2),0),2)
You could also still use a wildcard if you re-cast the formula using an IF statement:-
=INDEX(I1:M1000,MATCH(A2&"*",IF(M1:M1000=B1,I1:I1000),0),2)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
