IF Formulas with data from Multiple Cells

Hi there,

I need help building a formula with data from two different cells, producing data in a third cell. I originally created a massive IF statement formula, but I believe I maxed out the permitted quantity of nested IF statements and the formula won't work.

I want to pull data from two cells, compare it to data on a separate worksheet, and pull data from a cell on that work sheet. So if I type a product in Column A, and a Purchaser in Column B, I want a formula in C:2, that will use the data from both cells to find the correct value from the second worksheet titled 'Rates'

Sample Below:

My real data has 5 products and 7 different Purchasers, but could be more in the future, at which point I would need to adjust the formula presumably.

TIA

Formulas & Functions

Need Help

SergeiBaklan
Jun 19, 2018
Hi Jeannelle,

Sorry, MATCH:s shall be in opposite order, wrote formula from picture without the file.

=IFERROR(INDEX(Rates!$B$2:$E$5,MATCH($A2,Rates!$A$2:$A$5,0),MATCH($B2,Rates!$B$1:$E$1,0)),0)

Test file is attached
IndexMatchFruitsNames.xlsx11 KB

SergeiBaklan
MVP
Jun 18, 2018
Hi Jeannelle,

In C2 it could be

=IFERROR(INDEX(Rates!$B$2:$E$5,MATCH($B2,Rates!$B$1:$E$1,0),MATCH($A2,Rates!$A$2:$A$5,0)),0)
- Jeannelle Montroy
  Copper Contributor
  Jun 19, 2018
  Hi Sergei,
  
  I appreciate your formula, but it doesn't seem to be working quite perfectly. For example, if i put Sue & Orange, I am getting a unit rate of $3.50 instead of $2.00
  - SergeiBaklan
    MVP
    Jun 19, 2018
    Hi Jeannelle,
    
    Sorry, MATCH:s shall be in opposite order, wrote formula from picture without the file.
    
    =IFERROR(INDEX(Rates!$B$2:$E$5,MATCH($A2,Rates!$A$2:$A$5,0),MATCH($B2,Rates!$B$1:$E$1,0)),0)
    
    Test file is attached
    
    IndexMatchFruitsNames.xlsx11 KB

Forum Discussion

IF Formulas with data from Multiple Cells