Excel: Find multiple words and display result in next column?

Question

Hello,

I have a list of data which is is one long column. What I need excel to do, is to search each row and display the correct result in the column next to the row.

So for instance, If I have 'apple' in b10. I would want excel to display 'fruit' in c10. Now there might be other words for the result of fruit which could be banana or melon etc.
Cell b11 might include the word 'carrot' for which I would like excel to select 'vegetable' in cell c11. Also as before, there might be multiple words to search for the result of 'vegetable'.

The next thing is that I want to create a macro for this procedure. So if the next data sheet has 11 entries or 200 entries it will still pull up the same required data.

I am not entirely sure how to complete this. I'm wondering if I would need to access VBA to include these multiple entries, which could be for over 20 different results.

Thanking you,


Sean

Answer 1

Need to make a lookup table with all the fruits and vegetables entered, then the specific category each is classed as. To extract data there are 2 formula VLOOKUP and INDEX

B2=VLOOKUP(A2,X:Y,2,FALSE)

B2=INDEX(Y:Y,MATCH(A2,X:X,0))

Copy these down your list length

X&Y are your lookup table

Answer 2

1

Answer 3

Paste this formula in B1, then copy across. =AVERAGE(INDIRECT("A1:A"&COLUMN()))