vlookup with wildcard not working

[DEEARO]

Hi,

Can anyone help please...…

I am trying to use vlookup with wildcard but it doesn't seem to work.

[XLent]

If I've understood:

Please Login or Register  to view this content.

[BMV]

Please Login or Register  to view this content.

[DEEARO]

thank you very much this has been bugging me for ages. if you don't mind helping me another issue. I have attached the excel sheet. your help is much appreciated

[XLent]

not sure I fully understand the requirement but, perhaps:

Please Login or Register  to view this content.

note: I opt to apply a space delimiter to both description (source) and search terms, in these scenarios, to mitigate risk of false positives - e.g. "car" + "carpet"

[avk]

may be "D6"

Please Login or Register  to view this content.

"E6"

Please Login or Register  to view this content.

copy paste down

[DEEARO]

worked perfectly thank you so much

[DEEARO]

not sure I fully understand the requirement but, perhaps:

Please Login or Register  to view this content.

note: I opt to apply a space delimiter to both description (source) and search terms, in these scenarios, to mitigate risk of false positives - e.g. "car" + "carpet"

worked perfectly

[DEEARO]

If I've understood:

Please Login or Register  to view this content.

not sure I fully understand the requirement but, perhaps:

Please Login or Register  to view this content.

note: I opt to apply a space delimiter to both description (source) and search terms, in these scenarios, to mitigate risk of false positives - e.g. "car" + "carpet"

I found a slight issue, when searching a word and its without a space then it does not locate, e.g. Screwfix.com then it does not find a respond

[XLent]

please don't quote entire posts... just quote relevant parts (if necessary).

For your specific request, either

a) replace the non-space word delimiters with a space, e.g.

=IF(COUNT(C12),LOOKUP(9.99E+307,SEARCH(" "&TRIM(DEE[KEY])&" "," "&SUBSTITUTE($A12,"."," ")&" "),DEE[[DESCRIPTION ]]),"")
confirmed with Enter (not Array)

or

b) accept risk of false positives and revert to an approach without delimiter (provided earlier by another)

=LOOKUP(9.99E+307,SEARCH($I$6:$I$11,$A12),$J$6:$J$11)
confirmed with Enter

the risk of b is simply if your criteria values (Col I) are not mutually exclusive... e.g.

Col I contains both "carrot" and "car", with "food" and "repairs" as respective categories...

If your source data read "carrot cake" you would always get "repairs" returned as this would be the last valid match in your search terms (car)

so, if using b, just be careful how you sort your values in Col I.

[DEEARO]

For your specific request, either

a) replace the non-space word delimiters with a space, e.g.

=IF(COUNT(C12),LOOKUP(9.99E+307,SEARCH(" "&TRIM(DEE[KEY])&" "," "&SUBSTITUTE($A12,"."," ")&" "),DEE[[DESCRIPTION ]]),"")
confirmed with Enter (not Array)

this works in most places but I still have few area in my data sheet


for example CARD PAYMENT TO ISLEWORTH,1.60 GBP, RATE 1.00/GBP ON 10-12-2019, shows N/A as I am looking up Isleworth

[XLent]

Two things

1. as mentioned earlier - you need to replace any word separator that is not a space with space (to use option a)

so you need to SUBSTITUTE both the full stop, and the comma, e.g.

=IF(COUNT(C13),LOOKUP(9.99E+307,SEARCH(" "&TRIM(DEE[KEY])&" "," "&SUBSTITUTE(SUBSTITUTE($A13,"."," "),","," ")&" "),DEE[[DESCRIPTION ]]),"")
the more delimiters you have the more complex this can become, obviously

2. in your sample the spelling of Isleworth is incorrect in Col I

[DEEARO]

I AM REALLY SORRY FOR ALL THE QUESTIONS.

All your formulas work in my actual table that I am using after editing table name and row/Coolum, however when I am typing the new formula into excel I get an error and excel auto changing it (see below) and I believe this preventing it from working in my table, any ideas please.

=IF(COUNT(D2),LOOKUP(9E+99+307,SEARCH(" "&TRIM(SOUT1[KEY])&" "," "&SUBSTITUTE(SUBSTITUTE($B2,"."," "),","," ")&" "),SOUT1[DESCRIPTION]),"")

[AliGW]

Try this:

=IF(COUNT(D2),LOOKUP(9999999999999999,SEARCH(" "&TRIM(SOUT1[KEY])&" "," "&SUBSTITUTE(SUBSTITUTE($B2,"."," "),","," ")&" "),SOUT1[DESCRIPTION]),"")

[DEEARO]

=IF(COUNT(D2),LOOKUP(9.99E+307,SEARCH(" "&TRIM(DEE[KEY])&" "," "&SUBSTITUTE(SUBSTITUTE($B2,"."," "),","," ")&" "),SOUT1[[DESCRIPTION ]]),"")
=IF(COUNT(D2),LOOKUP(9999999999999999,SEARCH(" "&TRIM(DEE[KEY])&" "," "&SUBSTITUTE(SUBSTITUTE($B2,"."," "),","," ")&" "),SOUT1[DESCRIPTION]),"")

Both these formulas are giving me errors, even when the previous formula was finding results. The previous formula works better but does work in every scenarios e.g CARD PAYMENT TO ISLEWORTH,1.60 GBP, RATE 1.00/GBP ON 10-12-2019

=IF(COUNT(C12),LOOKUP(9.99E+307,SEARCH(" "&TRIM(DEE[KEY])&" "," "&SUBSTITUTE($A12,"."," ")&" "),DEE[[DESCRIPTION ]]),"")

[XLent]

given the mixed references in the formulas above, both range & table, it's hard to offer any guidance.

Referring back to post#12, the formula provided worked for the sample file provided, once the misspelling of Isleworth was corrected in the reference table.

If what you have is not working this implies that either

a) you have copied the formula incorrectly and/or are applying the wrong cell references,
b) you have not corrected misspellings, or
c) you have other delimiters to account for that you've yet to illustrate.

[DEEARO]

Hi have uploaded a sample of the excel sheet I been using. The Formula is not working (sheet 1). The Ref Sheet is the where the 2 tables are for the key and description are.

[XLent]

your issue is that you now have blank search terms, a new development / requirement - change in red below.

=IF(COUNT(C2),LOOKUP(9.99E+307,SEARCH(" "&TRIM(DEE[KEY])&" "," "&SUBSTITUTE(SUBSTITUTE($B2,"."," "),","," ")&" ")/(DEE[KEY]<>""),DEE[DESCRIPTION]),"")
confirmed with Enter

you will still need to wrap the above in an IFERROR handler to handle things with no match - i.e.

=IFERROR(IF(COUNT....),""),"")

[DEEARO]

please show me in the excel sheet as not sure how to implement the formula

[DEEARO]

I have uploaded a spreadsheet with both the new and previous formula. the new formula doesn't seem t be working on my spreadsheet.

[XLent]

you are not using the formula provided for your prior requirement, which is not an array formula.

there's nothing further I can add to this thread.

please close, and start a new thread as your requirements have changed, yet again.

edit:
per below, Mod has stated you may continue to deviate the thread from original requirement

[AliGW]

No - please do NOT start a new thread. The issue should remain here, even if the requirements have changed.

Thanks.

[DEEARO]

hi Xlent I have not changed the requirements, it is the same. I am only using the formula mentioned on this forum. Column G is your previous formula which is working
IF(COUNT(D3),LOOKUP(9E+99+307,SEARCH(" "&TRIM(SOUT1[KEY])&" "," "&$B3&" "),SOUT1[DESCRIPTION]),"")

and column F is not working
=IF(COUNT(D3),LOOKUP(9999999999999990,SEARCH(" "&TRIM(SOUT1[KEY])&" "," "&SUBSTITUTE(SUBSTITUTE($B3,"."," "),","," ")&" "),SOUT1[DESCRIPTION]),"")

[AliGW]

Did you see the message to you in one of your other threads?

You need to stop opening threads on issues that you claim are different, but are in fact all ostensibly the same.

Please nominate ONE of your threads to keep open, and I will close the rest. Do you want this one to stay open?

[DEEARO]

Hi Ali we have already discussed this. I already mentioned i have three threads open and they all different, and you said that's fine. Don't understand why this issue has been brought up again

[AliGW]

If you look at the time stamp of the post, you'll see that it was made before our 'discussion'.

I am not alone in feeling that you are opening too many threads on interconnected issues, thus creating a certain amount of confusion, so please don't open any more until the current ones are resolved. After that, you really are much better off dealing with one issue at a time.

[DEEARO]

Hi Ali, going forward i will create 1 thread at a Tim be.

[DEEARO]

Hi all

Please look at post 20, anyone able to solve please?

[DEEARO]

Hi all

Please look at post 20, anyone able to solve please?

[BMV]

Please Login or Register  to view this content.

[DEEARO]

Thank you BMV for helping me out. The solution has solved some areas, but sometimes the old formula finds results but your new formula does not. Please see attach excel sheet.

[BMV]

I don't know the reason to clear text from dot and comma but it must be done for both part

Please Login or Register  to view this content.

[DEEARO]

Hi BMV

i have tried your new formula I am getting errors. Please see attached spreadsheet. If you get the formula to work, please upload an excel with it working, makes it easier

[BMV]

look at the #32

[DEEARO]

hi BMV the file doesnt seem to be working for me, please kindly can you reupload it with your new forumala

[BMV]

Did you try attached file?

[DEEARO]

Hi BMV, thank you very much. I got it to work. The problem has now been solved.

Everyone thank you soooooooooooooooooooooooooooooooooo much for all the help and quick responses.