# Multiple IF statements? Or VLookup?

#### Lisa_King_3

##### New Member
Hi people

I have a set table in one worksheet, with three columns: Age, Type and Fee. The table has 12 rows, with a combination of ages, types, and prices.

In another worksheet, i have the weeks sales. This table has column headers: age, type, fee.

I need a formula that will return a the value in the third column(fee) on the first sheet, if the text in the first two columns(age and type) match up with the text in the first two columns of the table for the weekly sales?

I have tried to do it with IF and AND functions, but i come up with really long formula's that don't work? I hope you can help me.

I thought that maybe i could do it using VLookup, but i dont know how to do VLookup with looking up the value in two columns rather than just one?

Lisa x

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Lisa_King_3 said:
...
I have a set table in one worksheet, with three columns: Age, Type and Fee. The table has 12 rows, with a combination of ages, types, and prices.

What is the name of the sheet and the range of the table this sheet houses?

In another worksheet, i have the weeks sales. This table has column headers: age, type, fee.

...

Which sheet is it and in which cell do you have the first age value?

1) Sheet: "Membership Prices" / Table "Prices" Range: A14:C25

2) Sheet: "Weekly" / First Age Value: B9

This is what my tables look like:

1)
Age/ Type/ Fee
Associate/ Annual/ £1.00
Associate/ Monthly/ £2.00
Associate/ Single/ £3.00
Concession/Annual/ £4.00
Concession /Monthly/ £5.00
Concession /Single/ £6.00
Senior/ Annual/ £7.00
Senior/ Monthly/ £8.00
Senior/ Single/ £9.00
Vet/ Annual/ £10.00
Vet/ Monthly/ £11.00
Vet/ Single/ £12.00

2)
Age/ Type/ Fee
?/?/?
(Data will vary here)

Lisa_King_3 said:
1) Sheet: "Membership Prices" / Table "Prices" Range: A14:C25

2) Sheet: "Weekly" / First Age Value: B9

This is what my tables look like:

1)
Age/ Type/ Fee
Associate/ Annual/ £1.00
Associate/ Monthly/ £2.00
Associate/ Single/ £3.00
Concession/Annual/ £4.00
Concession /Monthly/ £5.00
Concession /Single/ £6.00
Senior/ Annual/ £7.00
Senior/ Monthly/ £8.00
Senior/ Single/ £9.00
Vet/ Annual/ £10.00
Vet/ Monthly/ £11.00
Vet/ Single/ £12.00

2)
Age/ Type/ Fee
?/?/?
(Data will vary here)

Since your fee table shows a nice regularity, would you be willing to change the lay out in Membership Pricesto:
Book2
ABCD
15Type
16AgeAnnualMonthlySingle
17Associate1.002.003.00
18Concession4.005.006.00
19Senior7.008.009.00
20Vet10.0011.0012.00
Membership Prices

Is this OK?

yes that's fine

Lisa_King_3 said:
yes that's fine

Great, otherwise I was going to suggest a multi-key lookup with concatenation, while with the new lay-out we can jus have:
Book2
BCDE
8
9SeniorAnnual7.00
10
11
Weekly

The formula in D9 is:

=VLOOKUP(B9,'Membership Prices'!\$A\$17:\$D\$20,MATCH(C9,'Membership Prices'!\$A\$16:\$D\$16,0))

it didn't work, i used the same test data that you did, senior annual, but it returned 4 rather than 7?

oh no, sorry, it has worked, i made a mistake when typing in the formula! t

Lisa x

Replies
10
Views
269
Replies
9
Views
166
Replies
3
Views
126
Replies
1
Views
101
Replies
1
Views
363

1,217,317
Messages
6,135,836
Members
449,965
Latest member
Ckl43

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back