Lookup for MULTIPLE values and return a specific cell if AT LEAST ONE criteria is met

[simonesk]

0


I am trying to find a specific value (from multiple options) in an array (Please, see the picture below). I tried several formulas, playing with OR, HLOOKUP, INDEX and MATCH, but I couldn't find a working formula yet. It is all good if I look for a single value, but as soon as I add multiple criteria I get #VALUE or #N/A, or similar errors.

Could you be so kind to help me out with this matter?

Regards,

Simone

MyData:

Borehole Week1 Week2 Week3 Week4 Wanted output: Lookup Values: W 18/10 W 25/10 W 01/11 W 08/11
ML 110 06/10/2021 W 01/11 ML 110
ML 111 W 18/10 ML 111
ML 114 W 25/10 ML 114
ML 117 10/10/2021 11/11/2021 W 06/12
ML 119 W 08/11 ML 119
ML 120 25/10/2021 W 01/11 ML 120
ML 129 19/09/2021 22/10/2021 05/11/2021 W 06/12

[6StringJazzer]

It is SO much better to attach your file instead of a screen shot. Easier too.

Also, it is better to state the commercial release of your Excel version rather than the version number (e.g., Excel 2019, Office 365). The version number is probably more precise but that's not how Microsoft describes what features are in what release.

In G2 enter the following array formula:

Please Login or Register  to view this content.

This is an array formula. After typing in the formula, do not hit ENTER--hit CTRL+SHIFT+ENTER. You have done it correctly if the formula in the formula box has {braces} around it. You cannot type in the braces; they are just an indicator that it is an array formula.