SOLVED
Home

need help to come up with a formula

MDN-5
New Contributor

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 Replies
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

 

Highlighted
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
Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies