# Match and Index

#### LeeGarner

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

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### steve the fish

##### Well-known Member
Do you have a table of rates? If so can we see it?

#### LeeGarner

##### New Member

<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

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

##### Well-known Member
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

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

Replies
0
Views
266
Replies
3
Views
197
Replies
16
Views
527
Replies
2
Views
483
Replies
9
Views
277

1,191,630
Messages
5,987,774
Members
440,109
Latest member
mitra2022

### 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