Forum Discussion

MDN-5's avatar
MDN-5
Copper Contributor
Jan 01, 2019
Solved

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) Colum...
excel
  • Yury Tokarev's avatar
    Yury Tokarev
    Jan 02, 2019

    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

     

Yury Tokarev's avatar
Yury Tokarev
Iron Contributor
Jan 02, 2019

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

 

MDN-5's avatar
MDN-5
Copper Contributor
Jan 04, 2019
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

Resources