Using multiple cells with words to bring back a price

**Fun2Dream** · 01-29-2015, 01:39 PM

Hello

I am trying to get a formula to help people know a price for a specific car service. Variables are selected from a list.

For example - the variables in the list in A2 are TEXT: Car 1, Car 2, Car 3
Variables in B2 are TEXT: Pre March 2013 and Post March 2013
Variables in C2 are TEXT: Year 1, Year 2, Year 3, Year 4

Basically I want to be able to pull from a hidden sheet the price that relates to any combination of the above

EG Sheet 1 A2=Car2 / B2=Pre March / 2013 C2=Year3 ...... returns data from Sheet 2 cell 3 which is a price.

**npatridge** · 01-29-2015, 01:48 PM

If you could, post a sample Workbook so we know how to write the formula. Alternatively, I think this link will be useful for you:

http://exceltactics.com/vlookup-mult...g-index-match/

**daffodil11** · 01-29-2015, 01:48 PM

=SUMPRODUCT((Sheet2!$A$1:$A$200=A2)*(Sheet2!$B$1:$B$200=B2)*(Sheet2!$C$1:$C$200=C2)*(Sheet2!$D$1:$D$200))

OR

=SUMIFS(Sheet2!$D$1:$D$200,Sheet2!$A$1:$A$200,A2,Sheet2!$B$1:$B$200,B2,Sheet2!$C$1:$C$200,C2))

**Glenn Kennedy** · 01-29-2015, 01:51 PM

Something lke this, maybe.

it's an Array Formula, ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.