CODEWITHSUNDEEP
excelexcel-formulavba
Jai
Jai

Reputation: 8363

Excel: Row search based on multiple column criteria

Let's say I have this:

Input1 Input2 Input3 Input4 Output1 Output2 Output3
  1      1      1      1      4       20      11
  1      12     7      14     18      11      8
  4      2      18     7      0       6       16

The input values form a unique key (like a composite primary key in SQL). So each input column could have repeated values, but the whole set can't repeat.

Then I have this for searching and displaying data:

Input1 Input2 Input3 Input4 Output1 Output2 Output3
  A      B      C      D       E       F       G

A to D are the cells that I could key in. Based on these 4, E to G would find the correct row and display the correct output values.

I've tried using vlookup as an array formula (CSE), but it is not giving me the correct answer. It is returning me the value on the last row that Input1 matches (regardless if other matched). Anyone knows how I can achieve this?

Edit

This is the formula I tried for the cell in E:

{=VLOOKUP(I2:L2, A2:G4, 5)}

where I2:L2 is the range with cells of values A to D, A2:G4 is the whole set of original data.

The formula evaluates to 18 when the inputs are 1,1,1,1, whereas this should match with the first row and evaluates to 4.

Upvotes: 1

Views: 376

Answers (1)

user4039065
user4039065

Reputation:

If A:D make unique combinations then you can use SUMIFS like this in M2,

=SUMIFS(E:E, $A:$A, $I2, $B:$B, $J2, $C:$C, $K2, $D:$D, $L2)

Drag right and down.

If A:D do not make unique combinations then you require a multi-column lookup, probably with INDEX/AGGREGATE; you also need to know which four-column match you want to retrieve.

enter image description here

Upvotes: 2

Related Questions