• 470K Members
• 5,633 Online
• 568K Conversations
SOLVED

Highlighted
New Contributor

# need help to come up with a formula

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

# Re: need help to come up with a formula

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

# Re: need help to come up with a formula

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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies