Multiple IF statements? Or VLookup?

Lisa_King_3

New Member
Joined
Jan 26, 2004
Messages
39
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Jan 26, 2004
Messages
39
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)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Jan 26, 2004
Messages
39
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
Joined
Jan 26, 2004
Messages
39
oh no, sorry, it has worked, i made a mistake when typing in the formula! t

cheers for your help :wink:

Lisa x
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

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?

Disable AdBlock

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
Top