Issue : VLOOKUP (XLOOKUP) not looking a pass "."

Is this is so? That Vlookup / Xlookup doesn't look passed a period (.).

I want to search in a string (array of strings) the file extension.

So I have the following table.

Anti-Slip Material Research.pptx
Results of DPB pallet matetial analysis.pptx
Mechanical Properties.xlsx
Materials Information.pptx
Setup.xlsx
Overview tilo crates (colors).xlsx
NewsLine.vsdx
Overall Planning.pdf
Overall Planning.vsdx
Thumbs.db
afspraak Anti stick MB tbv verkleuring.msg
Afspraken van afgelopen week 5-2015 .msg
Anti Stick voorstel(Lawrence) tbv grijsschaal probleem.msg
AW Was anlage ( fuer anti stick und ICE label).msg
bondingtest .msg
Colomn test antistick material .msg
Droptest antistick nr 1 en 2 .msg
Foto's Quv.msg
FW  .msg

And I want to search for file extension and return the cell value.
Meaning if I search for ".pdf" I should get all the value that has a .pdf in this case it is Overall Planning.pdf
But Vlookup/Xlookup can't handle this. They stop "search the string" left to the '.' period sign.

I use the following formula I get the values I want.

=IFERROR(INDIRECT("A"&IF(ISNUMBER(SEARCH($B$2;A2;4));ROW();""));"")

How I want to condens the result removing 'blank' cells.

I want to learn how to fix or help with this use for VLOOKUP/Xlookup not reading pas a period sign '.'.

thanks
-=CWever=-

You can use wildcard characters with VLOOKUP (and a few other functions), so you could do this:

=IFERROR(VLOOKUP("*"&B2,A:A,1,0),"not found")

assuming B2 contains ".pdf".

Hope this helps.

Pete

EDIT: change the commas ( , ) to semicolons ( ; ) if your regional settings expect that.

Originally Posted by Pete_UK

You can use wildcard characters with VLOOKUP (and a few other functions), so you could do this:

=IFERROR(VLOOKUP("*"&B2,A:A,1,0),"not found")

assuming B2 contains ".pdf".

Hope this helps.

Pete

EDIT: change the commas ( , ) to semicolons ( ; ) if your regional settings expect that.

Pete,
So am I right that it can't search with that "wildcard character" without ""?

Thanks that help.
-=CWever=-

The normal operation for VLOOKUP is to find a match in the lookup_range, by seeing if the sought_item exists in one of the cells of that range. However, by using wildcard characters you can check to see if the sought_item is contained within one of the cells (as a sub-string). You can add the wildcard character(s) before the sought_item (as in this case), so it will look for a match at the end of the string, or you can add it to the end, in which case it will check to see if any cells begin with the sought_item, or you can add it to both ends, in which case you will be looking to see if the sought_item exists anywhere within the cells of the lookup_range. Thus, you have that flexibility to search for sub-strings.

Hope this helps, and thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete