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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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