Hi Paul

Give this a try.

It uses ARRAY formulas so you must press CTRL+SHIFT+ENTER instead of ENTER when you edit the formula.

Hope this helps.

Sunny

Thanks Sunny

Great help

Hi Sunny,

I tried applying the formula to my spreadsheet but could not get it to work, not sure if the issue is because the lookup values are on another worksheet within the workbook?

Any help gratefully accepted

Regards

Paul

Hi Paul

Give this a try.

Sunny

Hi Sunny,

I'm still not able to get the formula to work on my worksheet? Is it because in my worksheet the Hotel column is before the Hotel Group column? and my workbook is sorted by Hotel where as yours is by Hotel Group?

Finally your formula has { } these brackets, not sure where these have come from?

So sorry to be a pain, perhaps you could update the formula in the attached so I can see where I'm going wrong?

Thanks

Paul

Hi Paul

Please refer attachment.

The formulas are ARRAY formulas and need to be completed by CTRL+SHIFT+ENTER instead of ENTER.

The curly brackets {} are automatically inserted by Excel. You don't key them in.

You can refer https://www.myonlinetraininghub.com/excel-array-formula for more detail about ARRAY formulas.

I could have given you a VBA solution but not sure if you would prefer it.

Whenever possible, make sure that your data is in a tabular format. It will make life more easy when you need to extract data using formulas,Pivot Table etc.

You can learn more about tabular format here  https://www.myonlinetraininghub.com/excel-tabular-data-format

Hope this helps.

Sunny

Thanks Sunny,

I just forgot to do CTRL+SHIFT+ENTER instead of ENTER. when I put the formula in.

Works perfect now.

As usual you were a fantastic help

Regards

Paul