Hi,
I am currently trying to get VLookup to keep searching if the first match it finds has an empty cell.
For example;
Column A Column B Vlookup name Vlookup value
Pizza place A Pizza place A 0
Pizza place A 10
Pizza place B 15
How do I make an if statement or similar for the Vlookup to see the first match is 0, keep searching, add the value for the next match which in this case will be "10" for Pizza place A?
Thanks
There are instructions at the top of the page explaining how to attach your sample workbook.
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.
short answer - you cant. Vlookup finds the 1st match, then stops looking.
If you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like, we can see if there is another approach we can take.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Re: How to get VLookup to keep searching
Please see attached example
Try this:
=LOOKUP(2,1/(($A$2:$A$4=C2)*($B$2:$B$4<>"")),$B$2:$B$4)
What about adding in an if statement to tell excel to run another identical Vlookup buttom up instead of bottom down?
See post #5.
I am just getting an error saying "theres a problem with this formula" when I try copy it in. Could you explain how this formula works?
Change all the commas to semi-colons for your European locale:
=LOOKUP(2;1/(($A$2:$A$4=C2)*($B$2:$B$4<>""));$B$2:$B$4)
It finds the first match to meet both criteria (column A = C2 and column B is not blank).Could you explain how this formula works?
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Last edited by AliGW; 07-07-2020 at 11:24 AM.
Thanks you! That works
Last edited by AliGW; 07-07-2020 at 11:25 AM. Reason: Please don't quote unnecessarily!
You don't need to combine the two - just use the LOOKUP formula.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
One more thing before I mark it as solved. I also wish to add in wildcards for Pizza place A, so that the formula works if the place is just called "Place A" for example. I tried to do the following but didnt work?
=LOOKUP(2;1/(($A$2:$A$4=”*”&C2&”*”)*($B$2:$B$4<>""));$B$2:$B$4)
Did you try this?
=LOOKUP(2;1/(($A$2:$A$4=”*”&C2)*($B$2:$B$4<>""));$B$2:$B$4)
If not, attach another workbook.
I didnt manage to get it to work
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks