Match and Index

[LeeGarner]

Hi Guys, first off - Happy New Year to everyone!

I am trying to use multiple criteria in an Excel Invoice for a friend, however, I am stuck now because I have confused myself so much watching videos on Index and Match...

I have multiple criteria (of course)...

- There are multiple room Types A, B, B1, C, D and E (Dropdown)
- And each Room Typ and has either 1, 2 or 3 Person Prices (Table)
- These price DEPEND on number of nights (7-14, 15-27 and 28+)
- And additionally there is the type of Stay (Boarding Type) - Room Only, Bed and Breakfast and Half board (Dropdown) which in turn using IF statement returns the additional price for either Breakfast or Half-Board

So I did a MATCH for Room Type, an Amount given from a CELL containing Number of Persons, a MATCH for the Number of Nights (caclulated from arrival and departure date) and a MATCH for the Boarding Type - so now I have for example:

Typ A, 2 Persons, 17 Nights with Halfboard... how do I formulate the answer out of the MATCHES? Or am I am the wrong track?

Any help is greatly appreciated - Cheers
Lee

 

[steve the fish]

Do you have a table of rates? If so can we see it?

 

[LeeGarner]

<colgroup><col><col span="3"><col span="3"><col span="3"></colgroup><tbody>
</tbody>

Board Basis	RO	No Breakfast	0 Euro
	BB	Breakfast	11, Euro
	HB	Half Board	16, Euro
	7-13 D	7-13 D	7-13 D	14-27 D	14-27 D	14-27 D	28+ D	28+ D	28+ D
Type MM	1_1	1_2	1_3	2_1	2_2	2_3	3_1	3_2	3_2
A	79 €	95 €	122 €	74 €	90 €	117 €	69 €	85 €	112 €
B1	89 €	105 €	132 €	84 €	100 €	127 €	79 €	95 €	122 €
B1	91 €	107 €	134 €	86 €	102 €	129 €	81 €	97 €	124 €
C	106 €	122 €	149 €	99 €	117 €	144 €	94 €	110 €	137 €
D	133 €	165 €	197 €	123 €	155 €	187 €	117 €	149 €	181 €

<colgroup><col><col span="3"><col span="6"></colgroup><tbody>
</tbody>

From the above the 7-13 D is the number of days and underneath 1_1, 1_2, 1_3 for example is Price Catagory 1 _ 1 Person or 1_2 Persons etc

 

[LeeGarner]

I have just seen that the TYPE MM has B1 in it twice (these are the categories) and the first B1 should simply be a "B"

 

[steve the fish]

Ok so you have a cell that shows board basis, another that shows Type MM? Does the 1_1 appear in a cell somewhere also so it can just be matched in the table?

 

[LeeGarner]

Ok Thank you so much for your help but I found it now... I concated the number of persons and the price category using the "&" and it works

Thanks a lot for your willingness to help