# 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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

##### MrExcel MVP
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?

#### Lisa_King_3

##### New Member
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)

##### MrExcel MVP
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?

#### Lisa_King_3

yes that's fine

##### MrExcel MVP
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))

#### Lisa_King_3

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

#### Lisa_King_3

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

cheers for your help :wink:

Lisa x

Replies
1
Views
231
Replies
3
Views
175
Replies
5
Views
158
Replies
7
Views
124
Replies
4
Views
1K

1,171,072
Messages
5,873,638
Members
432,990
Latest member
Nuppu

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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