Excel wizards needed again - comparing data on multiple columns

Yella Fella

Yella Fella

Yella Fella

Associate
Joined
18 Apr 2004
Posts
335
Location
Milton Keynes, UK
I'm trying to figure out a formula that seems to be a vlookup but its not. I'll try and explain what I need...

I need to compare in sheet 1, column C which has a string of texts that I need to compare. The result will be in column D and needs to list what is in the header of sheet 2

The data/range is on sheet 2. There is 5 columns and over 200 rows of data. The data set for example is different types of fruit, veg, protein etc.

The idea is if it picks up any keywords in a string of text for say 'apple' 'orange' in sheet 1, column C. This will bring back a header title of 'Fruits' as it matches the data found on sheet 2, same for Veg etc.

I've worked out this formula which seems to be what it should do but it doesn't give me an error or anything. I see a 0 flash up before it disappears:

=IFERROR(INDEX(Modules!$A$1:$E$1,MATCH(C2, Modules!$A:$E,0)),"")

I know the cell data is there... in this case for C2 and the result in D2, the text string is Apple Maybe Water and the keyword 'Apple' is in cell A127 on sheet 2. This put the result as 'Fruits'.

Am I completely off or does someone know a better way of writing this to achieve what I'm looking for? Would a table be better for something like this?

So sheet 1 will always be overwritten with new data as I look to obtain the results every time I export a spreadsheet out.
 
Does the cell only contain the keyword, or does it contain the keyword plus some other data?

Excel functions generally operate on the cell, so if the cell contains "Apple Maybe Water" and you use match to find "Apple", it won't find it unless you do other manipulation first (either in cells or in the formula).

You could do something like this with a helper column.

stackoverflow.com

Excel - Match substring from list of choices - INDEX, MATCH, and FIND used together

I'd like to search for a specific movie title within a list of video titles, search for MATCH, and use Index to return its description. I know this can be done with a text search in a filter via Co...
stackoverflow.com stackoverflow.com

Or you could write a custom VBA function to do it if you are doing it regularly, or, depending on the wider context, you may want to consider using a more suitable tool. It looks more like a relational data set than a spreadsheet at first glance.
 
You're not doing this in Excel with formulas, or if you are it will be so convoluted you may as well not bother.

If it was a single word you're looking for then maybe a really long nested IF with a FIND in there might work but trying to find a match from one of a set of words is VBA territory at best.
 
FruitVeg.png


In cell G2:
Code: 
=LET(
   Table_Range,        $A$2:$C$10,
   Header_Range,       $A$1:$C$1,
   Text_String,        $F2,
   Build_Lookup_Table, HSTACK(
                          TOCOL(Table_Range,3,TRUE),
                          TOCOL(IF(NOT(ISBLANK(Table_Range)),Header_Range,NA()),3,TRUE)),
   Words_from_Text,    TEXTSPLIT(Text_String," "),
   Lookup_Words,       VLOOKUP(Words_from_Text,Build_Lookup_Table,2,FALSE),
   Remove_Errors,      TOROW(Lookup_Words,3),
      Remove_Errors)

That what you're looking for?
 
You must log in or register to reply here.
Back
Top Bottom