Using Wildcards in a vlookup and/or Index Match

[SRSEV6]

I am trying to look up two criteria in my vlookup and/or index match. Doesn't matter to me which one to use as I am having issues with both.

One of the criteria I am searching for is a wildcard match. I need to search for criteria in the middle of not in the beginning or end of.

So for instance, here is my sample. The first snapshot is my existing Pivot Table

Invoice/WO ID and Line ItemINV AMTWO AMT
Network Company
Site 1
EF-Power$550.00$550.00
EF-Cable$5,250.00$5,250.00
EF-Tray$5,250.00$5,250.00
Site 1 Total$11,050.00$11,050.00
Site 2
EF-Power$659.93$659.93
EF-Cable$659.93$659.93
EF-Tray$1,530.00$1,575.00
Site 2 Total$2,849.86$2,894.86

The second snapshot is my other existing Pivot Table:

PO ID and Line ItemPO AMT
Site 1
Task 1 EF-Power-T03$550.00
Task 2 EF Cable-T04$5,250.00
Task 3 EF Tray-T05$5,250.00
Site 2
Task 1 EF-Power-T03$659.93
Task 2 EF Cable-T04$659.93
Task 3 EF Tray-T05
$1,575.00

In the first pivot table I want to add a column and pull the data over from the second pivot table. The two formulas I am using is as follows:

=VLOOKUP("*"&A4&"*"&A3,$A$4:B1000,2,0)

So I am trying to pull the PO AMT in the second pivot table for "Site 1" and "EF-Power". Site 1 (A3) will be an exact match however, EF-Power (A4) will not be an exact match. So want to use a wildcard.

The index match formula is:

=INDEX($F$4:$G$10000,MATCH($A3,$F$4:$F$10000,0),MATCH("*"&$A4&"*",$F$4:$F$10000,0))

Both pivot tables are in the same sheet. Some of the line items in the second pivot table may or may not have a hyphen (-) in the wording as well. The word I am looking for in each site # may be in front, in the end, or in the middle. I added the wildcard to the front and back of A4. I tried in front and the end but neither worked (A4&"*") and ("*"&$A4)

I have attached a snapshot of the spreadsheet in case I didn't match up the cells properly in the above formulas.

Of course this is a made up sample of the real spreadsheet.



Thank you so much for your help.

 

[vletm]

SRSEV6
A sample Excel file would be better than any snapshot or PDF.

but eg vlookup:
https://www.excelforum.com/excel-ge...-for-partial-text-match-and-return-value.html

 

[SRSEV6]

I have attached the actual sample spreadsheet. I look at the link you provided. Working on trying to look up multiple criteria with a wildcard in one of the criteria.

 

[bosco_yip]

Try…............

Formula solution for approx. match Lookup with duplicated criteria as per below picture.



1] In D4, copied down :

=IF(B4="","",IFERROR(INDEX(G$1:G$100,AGGREGATE(15,6,ROW(F$3:F$100)/ISNUMBER(SEARCH(A4,F$3:F$100)),COUNTIF(A$4:A4,A4))),""))

2] See attachment

Regards
Bosco

 

[vletm]

SRSEV6
As You case is Using Wildcards in a vlookup and/or Index Match
I gave You a link for vlookup-part, which gives one answer.
Have checked syntaxes of those functions?
There are rules, how those would work.
Sometimes users try to make own rules or variations which won't work.

Syntax
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
The lookup_value argument can be a value, a reference, or a text string.

=MATCH(lookup_value,lookup_array,match_type)
The argument lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
If match_type is 0 and lookup_value is text,lookup_valuecan contain the wildcard characters asterisk (*) and question mark (?). An asterisk matches any sequence of characters; a question mark matches any single character.

 

[SRSEV6]

Try…............

Formula solution for approx. Lookup as per below picture.

View attachment 58381

1] In D4, copied down :

=IF(B4="","",IFERROR(INDEX(G$1:G$100,AGGREGATE(15,6,ROW(F$3:F$100)/ISNUMBER(SEARCH(A4,F$3:F$100)),COUNTIF(A$4:A4,A4))),""))

2] See attachment

Regards
Bosco

Thank you so much! This is really nice. I will play around with it to make it work with the actual data I am using.

 

[SRSEV6]

SRSEV6
A sample Excel file would be better than any snapshot or PDF.

but eg vlookup:
https://www.excelforum.com/excel-ge...-for-partial-text-match-and-return-value.html

Thank you for the link.

 

[SRSEV6]

Thank you