Multiple IF statements? Or VLookup?

[Lisa_King_3]

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

 

[Aladin Akyurek]

...
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]

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]

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
	A	B	C	D
15		Type
16	Age	Annual	Monthly	Single
17	Associate	1.00	2.00	3.00
18	Concession	4.00	5.00	6.00
19	Senior	7.00	8.00	9.00
20	Vet	10.00	11.00	12.00
Membership Prices

Is this OK?

 

[Lisa_King_3]

yes that's fine

 

[Aladin Akyurek]

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
	B	C	D	E
8
9	Senior	Annual	7.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]

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]

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

cheers for your help :wink:

Lisa x