I am pulling data from a database containing Reservations for my property.

A limitation of that database is that if a guest has multiple units of the same room number, it lumps all of the "sub unit" in a single field.

I would like to have a formula that will quickly search for a specific "Room"/"Sub Unit" combination.

Any advice would be appreciated. Examples:

Data:

Query:

I have found that XLOOKUP does not like searching an array that spans multiple rows and is infinite.

This prompted me to try to separate the individual Sub Units from each line, but this adds an enormous workload since I have well over 2000 units to work with.

Is there a way to search for {6045} {B} in the Data sheet, returning that RES that corresponds with {6045} {AB}?

Thank you.

I am pulling data from a database containing Reservations for my property.

A limitation of that database is that if a guest has multiple units of the same room number, it lumps all of the "sub unit" in a single field.

I would like to have a formula that will quickly search for a specific "Room"/"Sub Unit" combination.

Any advice would be appreciated. Examples:

Data:

Query:

I have found that XLOOKUP does not like searching an array that spans multiple rows and is infinite.

This prompted me to try to separate the individual Sub Units from each line, but this adds an enormous workload since I have well over 2000 units to work with.

Is there a way to search for {6045} {B} in the Data sheet, returning that RES that corresponds with {6045} {AB}?

Thank you.

• excel
• excel-formula

Share Improve this question Follow edited Feb 4 at 20:41 z.. 13.3k22 gold badges99 silver badges2020 bronze badges asked Feb 4 at 18:00 John AndersonJohn Anderson 1333 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Use FILTER:

=FILTER(DataSheetName!A:A,(DataSheetName!C:C = B2)*(ISNUMBER(SEARCH(C2,DataSheetName!D:D))))