Excel 2007 : Using multiple Index-Match functions

**sigyn** · 10-13-2011, 10:18 AM

Hello,

I am having some issues with a workbook I'm working on. I'm trying to quote prices on one sheet from data from another spreadsheet. The idea is to type the name of the prescription, select the dosage, and it will give you the price from the data in the 'Prices' sheet.

However, I am having two problems that I think are associated with my choice to use Index-Match. First of all, if I have more than one prescription with the same name, it does not matter what dosage I choose it will give me the same price for both.

Second, sometimes when I type a prescription in the Generic column it will give me the dosage but the price won't show up.

Can anyone help me figure out what I am missing? I have attached an example of the workbook.

Thank you!

**NBVC** · 10-13-2011, 10:31 AM

Antara is listed twice with same dosage both times, but different prices? How do you decipher those?

**Nikeyg** · 10-13-2011, 10:33 AM

Is it worth inserting 2 columns into G and H, one for a concatenation of A and F and one for a concatenation of C and F?

=A2&F2
=C2&F2

And then putting this formula into your pricing in the quotes sheet?
In column D

=VLOOKUP(B3&C3,Prices!G:J,4,FALSE)

In column H

=VLOOKUP(F3&G3,Prices!H:J,3,FALSE)

Or is that not going to achieve your deseried results?

I'd hide the columns G and H in the prices spreadsheet too so they cannot be tampered with.

**sigyn** · 10-13-2011, 10:34 AM

Originally Posted by NBVC

Antara is listed twice with same dosage both times, but different prices? How do you decipher those?

Okay well that is an error on my part trying to quickly edit the spreadsheet to upload onto here. It shouldn't be the same thing twice, sorry. You can just ignore those.

**Richard Buttrey** · 10-13-2011, 10:46 AM

Hi,

Once you've chosen a Prescription Name, isn't the dosage and price dependent on the relevant row in the Prices table. i.e. there is only one dosage & price per prescription name. In which case a simple VLOOKUP would work.

Yet you seem to suggest that for any prescription name there are choices for dosage. Can you please give some specific examples of what you expect to see for various choices.

Regards

**NBVC** · 10-13-2011, 10:55 AM

Try this formula in D3:

=IF(OR(B3="",C3=""),"",SUMIFS(Prices!$H$1:$H$49,Prices!$A$1:$A$49,B3,Prices!$F$1:$F$49,C3))

copied down

and in H3:

=IF(OR(F3="",G3=""),"",SUMIFS(Prices!$H$1:$H$49,Prices!$C$1:$C$49,F3,Prices!$F$1:$F$49,G3))

copied down