Google Sheets Vertical Plus Horizontal Vlookup with multiple column in criteria and result based on-CodePudding

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.

CodePudding user response：

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:

=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)))))