Excel wizards needed again - comparing data on multiple columns

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.
 
Back
Top Bottom