Search code examples
excelexcel-formula

Formula to search for specific Chars within a cell

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:

enter image description here

Query:

enter image description here

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.

Solution

  • Use FILTER:

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