Formula to return the cost amount relative to the number of Adults & Dependants in a house

Please help. I need to write a simple formula that returns the cost amount relative to the number of Adults & Dependants in a household.
The table in the attached excel page details the relevant data and I just need a simple formula that allows me to obtain the correct cost in cell B17 if the appropriate numbers of adults and dependants are entered in cells B15 & B16.
Any help much appreciated. Thanks

Try this:

=LOOKUP(2,1/(($A$2:$A$13=B15)*($B$2:$B$13=B16)),$C$2:$C$13)

Then format the cell to match your cost cells.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Good luck to you Kiwis, too.

Tough luck on the cricket outcome - penalty shootouts are never really a nice way to have to finish - but we don't feel sorry for you at all.

I would do it this way:

Excel 2016 (Windows) 32 bit

	E	F	G	H	I	J	K
1		0	1	2	3	4	5
2	1	1,278	1,533	1,705	1,815	1,915	2,005
3	2	1,786	2,021	2,214	2,304	2,384	2,464
4
5			Adults	2
6			Dependants	6
7			Cost	2464

Sheet: Sheet1

=INDEX($F$2:$K$3,MATCH(H5,$E$2:$E$3,1),MATCH(H6,$F$1:$K$1,1))

That's why I reformatted the data.

What is the problem with having the lookup table horizontally? It makes everything easier.

EDIT: Forget that - I see what you've done.

You need the vertical (row) reference first:

=INDEX(F12:G17,MATCH(F20,E12:E17,1),MATCH(F19,F10:G10,1))

Syntax:

=INDEX(result_array,row,column)

Excel 2016 (Windows) 32 bit

	E	F	G
10	Adults	1	2
11	Dependants
12	0	1,278	1,786
13	1	1,533	2,021
14	2	1,705	2,214
15	3	1,815	2,304
16	4	1,915	2,384
17	5	2,005	2,464
18
19	Adults	1
20	Dependants	2
21	Cost	1,705
22

Sheet: 1

Excel 2016 (Windows) 32 bit

	F
21	=INDEX(F12:G17,MATCH(F20,E12:E17,1),MATCH(F19,F10:G10,1))

Sheet: 1

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Please would you update the thread title as requested in post #13, even though it's resolved? I confess I had not noticed that you had used a generic title, and I should have asked you to change it myself before we started.

Please change it to this: "Formula to return the cost amount relative to the number of Adults & Dependants in a household".

When this is done, please mark the thread as solved.

I have learnt what I know about Excel over a long period, and much of that knowledge has come from involvement here.

I'm sure we'll beat you again in Japan ...

Thanks for changing the title.