need help to come up with a formula

[MDN-5]

Hello, 

I am trying to figure out what is the formula that will work with the scenario I am looking for.

Example,

Worksheet 1 - list full of foods: 1) Column “title” of the food and 2) Column named as “Is this food spoiled” with answer Yes or No.

Worksheet 2 - list of weakness: 1) Column “title” of the food (same as Worksheet)

Since the list of foods already identified which food is spoiled or not. The list of weakness does not have the information which food is spoiled or not. I would like to add “Is this food spoiled” on the new column for Worksheet 2 gets the information from Worksheet 1. 

Thanks,

Matthew

[#2 · Accepted solution]

Hi Matthew,

you can use INDEX/MATCH for this purposes. 

Let's assume you have Sheet1 and Sheet2. Both sheets have 'Title', Banana' and 'Apples' entered in A1:A3. Sheet1 has got 'Yes' and 'No' in B2:B3. You can use the following formula in Sheet2!B2 to lookup for a value matching 'Banana':

=INDEX(Sheet1!$B$2:$B$3,MATCH(Sheet2!$A2,Sheet1!$A$2:$A$3,0))

If you copy this formula one cell down, it will show a lookup result for 'Apples'. 

Please see attached for an example

Thanks

Yury

[#3]

Thank you! I have more than two rows that need to check. I am almost there, I tried to add 400 on this formula:

=INDEX(Sheet1!$B$2:$B$400,MATCH(Sheet2!$A2,Sheet1!$A$2:$A$400,0))

Or, is there a way to create a formula for unlimited rows? Since there will be ongoing more foods.

Thanks,

Matthew