Bianca has attended:
Excel Advanced course
Excel Forecasting and Data Analysis course
Excel PowerPivot course
Excel Advanced - Formulas & Functions course
Excel VLOOKUP and SEARCH combination
Hi,
I'm trying to search for part of a text string, in a column of text and return the values in a second column.
if cell contains text "abc" within then return a specific lookup value.
I.e. column a has the following cells:
zebra black
dog white
elephant blue
In column B I need a formulae that looks in column E but returns values from column F. So if zebra black contains black then return value in column F
Column E Column F
BLACK $100
BLUE $200
GREEN
Thanks in advance for your help.
RE: Excel VLOOKUP and SEARCH combination
Hi Bianca,
Great question. There are some limitation with using searching, Wild characters and multiple options.
I've attached an example which adds an additional column and uses Index, Match and Right.
Let me know if that's a workable option for you
Kind regards
Richard Bailey
Microsoft Certified Trainer
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
Excel VLOOKUP and SEARCH combination.xlsxRE: Excel VLOOKUP and SEARCH combination
Hi,
Thanks for your help. Where can I get the attachment from?
Also, can I do the same in DAX using a combination of formulae's?
Thank you again
RE: Excel VLOOKUP and SEARCH combination
I have actually found the attachment but the text is not always in the right format i.e. cannot use right or left but thank you for the suggestion anyway.
I have found another way of solving that by using index, match(1,--not(not(search(x))
RE: Excel VLOOKUP and SEARCH combination
Hi Bianca,
Glad you found it.
I was just going with the example text you used, you are correct that would be a way of solving it.
You can use the wild character for search in DAX
https://msdn.microsoft.com/en-us/library/ee634235.aspx
Let me know if there's anything else we can help with.
Kind regards
Richard
