CODEWITHSUNDEEP
excelvba
Ajouve
Ajouve

Reputation: 10049

Search on multiple column vba excel

I have a document with datas and I want to automatize a research.

for exemple I have

| lastName | firstName | age | result

And I'd like to get the result row where lastName = Smith, firstName = Jhon and age = 42

I know how to do that with loops but the complexity is hight is there an other solution ?

Upvotes: 1

Views: 226

Answers (3)

Brandon R. Gates
Brandon R. Gates

Reputation: 318

In cases like this, I usually build a "lookup key" by using CONCATENATE() to combine the values. I can then return results using VLOOKUP() or SUMIF() as required since I find those functions intuitve and easier to write than range offset/index/match formulas.

Here's an image of a demo worksheet, upper left cell is "A1":

MultiLookup.xls

If there are multiple matches, you'll see the total count immediately. You can also use the Auto-Filter, etc., etc....

Here are the formulas in the filter row:

A3: =CONCATENATE(B3,C3,D3)
E3: =SUMIF($A$5:$A$10,$A$3,E$5:E$10)
F3: =VLOOKUP($A$3,$A$5:$F$10,6,FALSE)

Since F3 is using VLOOKUP() with option FALSE, it will only return the first result it finds in the list, otherwise it returns #N/A.

These are the formulas in the first row of data, which can be copied down to cover the entire range:

A6: =CONCATENATE(B6,C6,D6)
E6: =IF($A6=$A$3,1,"")
F6: =IF($A6=$A$3,ROW(),"")

Upvotes: 0

andy holaday
andy holaday

Reputation: 2302

CRondao has a good solution that will return the first match, and works on any data type. If result is numeric and you want to sum up all matches, this will work:

=SUMPRODUCT(--($A$1:$A$4="Smith"),--($B$1:$B$4="Jhon"),--($C$1:$C$4=42),$D$1:$D$4)

Note, this is not an array formula.

Upvotes: 1

CRondao
CRondao

Reputation: 1903

You can try This array formula:

=INDEX(D2:D10;MATCH(1;(A2:A10="Smith")*(B2:B10="John")*(C2:C11=42);0))

This is an array formula so you need Ctrl Shift Enter to enter the formula

Upvotes: 1

Related Questions