Index & Match multiple values

[jmcgallan]

Hello.

I'm trying to use the index and match function to give me a result from looking up to criterias.

=INDEX('Hotel Report'!$A$2:$AQ$412,MATCH('Hotel Rooms'!$A2,'Hotel Report'!$B$2:$B$174,0),MATCH('Hotel Rooms'!B$1,'Hotel Report'!$A$1:$AR$1,0))

Above is the formula I have used but all i get back is #N/A

I'm basically looking up if a room number has been used or not, however the room number may apear twice in the report.

Can anyone help, file attached

Thanks

[TMS]

It doesn't like the Room Numbers on the Hotel Report sheet.

For some reason, they look like numbers but they seem not to be, hence not matching the true numbers on the Hotel Rooms sheet.

You need to coerce the room numbers to be numeric. You can do this using Paste Special with an arithmetic function. Put a 1 in a free cell. Copy it. Select all the cells in the Room Number column. Choose Paste Special and Multiply.

Regards

[teylyn]

You can transform the numeric room numbers in the "Hotel Rooms" sheet into text values within your formula by concatenating them with an empty string

Hotel Rooms'!$A2&""

=INDEX('Hotel Report'!$A$2:$AQ$412,MATCH('Hotel Rooms'!$A2&"",'Hotel Report'!$B$2:$B$174,0),MATCH('Hotel Rooms'!B$1,'Hotel Report'!$A$1:$AR$1,0))

And you do not need to enter the formula as an array formula. A simple Enter will suffice. Copy across and down.

The remaining #N/A values are there because some room numbers, e.g. 909 are not listed in the "Hotel Report" sheet.

cheers,

[TMS]

@Teylyn: I like the alternative approach which means you don't need to change the data. I wonder why it's not consistent though, and if it should be.

@jmcgallan: I think this still leaves you with the problem of multiple entries as you'll get the first one for each room. I'm not sure of the best approach for that and it rather depends on what, if anything you want to change in your worksheet.