I am trying to get the result in Column M based on user input in column J,K and L by searching data from Column A to H. An example for reference is given below:
Any help on above will be appreciated.
1 Answers
see:
=INDEX(IFNA(VLOOKUP(J3:J10&K3:K10&L3:L10, QUERY(SORT(
{A3:A10&B3:B10&C3:C10, D3:D10;
A3:A10&B3:B10&E3:E10, F3:F10;
A3:A10&B3:B10&G3:G10, H3:H10}), "where Col2 is not null", ), 2, 1)))
update 1:
=INDEX(IFERROR(1/(1/IFNA(VLOOKUP(J3:J10&K3:K10&L3:L10, QUERY(SORT(
{A3:A10&B3:B10&C3:C10, IFERROR(D3:D10/0, 0);
A3:A10&B3:B10&E3:E10, IFERROR(F3:F10/0, 0);
A3:A10&B3:B10&G3:G10, IFERROR(H3:H10/0, 0);
A3:A10&B3:B10&C3:C10, D3:D10;
A3:A10&B3:B10&E3:E10, F3:F10;
A3:A10&B3:B10&G3:G10, H3:H10}), "where Col2 is not null", ), 2, 1)))))
update 2:
=INDEX(IF(REGEXMATCH(J3:J10&K3:K10, TEXTJOIN("|", 1, A3:A10&B3:B10)),
IFERROR(1/(1/IFNA(VLOOKUP(J3:J10&K3:K10&L3:L10, QUERY(SORT(
{A3:A10&B3:B10&C3:C10, IFERROR(D3:D10/0, 0);
A3:A10&B3:B10&E3:E10, IFERROR(F3:F10/0, 0);
A3:A10&B3:B10&G3:G10, IFERROR(H3:H10/0, 0);
A3:A10&B3:B10&C3:C10, D3:D10;
A3:A10&B3:B10&E3:E10, F3:F10;
A3:A10&B3:B10&G3:G10, H3:H10}), "where Col2 is not null", ), 2, 1)))), ))
Related