Hi,
Can anyone help please...…
I am trying to use vlookup with wildcard but it doesn't seem to work.
Hi,
Can anyone help please...…
I am trying to use vlookup with wildcard but it doesn't seem to work.
If I've understood:
Formula:Please Login or Register to view this content.
Please Login or Register to view this content.
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
not sure I fully understand the requirement but, perhaps:
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"Formula:Please Login or Register to view this content.
Last edited by XLent; 05-06-2020 at 03:05 AM. Reason: added note (re: rational for different approach to BMV)
may be "D6"Formula:Please Login or Register to view this content.
"E6"Formula:Please Login or Register to view this content.
copy paste down
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
worked perfectly thank you so much
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.
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
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
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]),"")
Try this:
=IF(COUNT(D2),LOOKUP(9999999999999999,SEARCH(" "&TRIM(SOUT1[KEY])&" "," "&SUBSTITUTE(SUBSTITUTE($B2,"."," "),","," ")&" "),SOUT1[DESCRIPTION]),"")
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
=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 ]]),"")
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.
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.
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....),""),"")
please show me in the excel sheet as not sure how to implement the formula
I have uploaded a spreadsheet with both the new and previous formula. the new formula doesn't seem t be working on my spreadsheet.
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
Last edited by XLent; 05-13-2020 at 05:24 AM.
No - please do NOT start a new thread. The issue should remain here, even if the requirements have changed.
Thanks.
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]),"")
Last edited by DEEARO; 05-13-2020 at 12:54 PM.
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?
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
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.
Hi Ali, going forward i will create 1 thread at a Tim be.
Hi all
Please look at post 20, anyone able to solve please?
Hi all
Please look at post 20, anyone able to solve please?
Please Login or Register to view this content.
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.
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.
Last edited by BMV; 06-15-2020 at 01:19 PM.
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
look at the #32
hi BMV the file doesnt seem to be working for me, please kindly can you reupload it with your new forumala
Did you try attached file?
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks