xlookup multiple criteria

[markomarra]

To Whom,

In a nutshell.

I have a column of stationing values that I would like to have the searched.

Find the interval between every PC and PT values in the search range and determine if another value is within that interval. If found return another value from a different column.

criteria:
1. PNT Type = "BL"
2. GEO. Type = ONLY the interval between PC and PT
3. BL = BL must Match Baseline
4. GEO. TIE = POT or POC

return value:
1. Radius Length.

Would prefer to use a excel formula. Maybe "xlookup" will work. but, only a suggestion/best guess. But if not possible a macro would suffice also.

Thanks in advance.

Mark

 

[awoohaw]

Mark, i just realized a potential for confusion. With the way you have the spreadsheet set up you there are multiple PT/PC scenarios and the value being searched are not in sequential order. Will there only be ONE and ONLY ONE (or NONE) circumstance where all the criteria are met? Otherwise this probably cannot be done with an excel formula and it will be very hard as it is.

 

[markomarra]

hi,

I have columns G thru I in a separate worksheet (the lookup values).
Columns A thru E (the lookup array) were put together in the same worksheet in order to post Mr. Excel..

When the value in each cell in column A equals "BL" AND when value in column E are the same Baseline as the previous cell value then column D is sequential.
Stated another way. Columns A thru E are sorted by column D then column E.

so when the lookup value in column H and corresponding cell value in column A is "BL" the lookup array is A3:E51
so when the lookup value in column H and corresponding cell value in column A is "SL" the lookup array is still A3:E51

therefore, the lookup array is always "BL" and are sequential. A3:E:51

the lookup value in column H will not always be sequential.

mark

 

[awoohaw]

okay, that is good to know. Thanks. Still is going to be a hard formula to build.