Is there a VLOOKUP equivalent of SUMIFS

[CDNcameron]

So I've come across tables before where I went to set up a look up that refers to more than one indicator and returns a result.

The table contains multiple areas, each containing districts all named 1-X. Is it possible to have a look up that will pull a result based on what is in two cells, the cells will be drop down lists, so the person can drop down, Area:WEST and District: D2 than the formula fills the result of B.

Area District Result
West D1 A
West D2 B
West D3 C
West D4 D
West D5 E
East D1 F
East D2 G
East D3 H
East D4 I
East D5 J

In the past I made seperate tables for each area, and used a VLOOKUP in combination with IF, so it would look in the WEST table IF the first one was WEST and so on.

Thoughts on how I could produce this with having the formula only refer to one table and not a mess of IFs (would be about 20or so otherwise)?

Thanks in advance

[daffodil11]

When you say more than one table, do you mean more than one column?

There are a couple options when performing multi-criteria string returns.

I prefer =LOOKUP(2,1/((criteria_range1=criteria1)*(criteria_range2=criteria2)),output_range)

So for example, you've got some colors in sheet2a1:A50, some numbers sheet2b1:b50, and some names in sheet2c1:c50.

=LOOKUP(2,1/((Sheet2!A1:A50="red")*(Sheet2!B1:B50=2)),Sheet2!C1:C50)

This returns the cell in C where A was red and B was 2.


You can freely nest in more criteria as needed for even bigger datasets:

=LOOKUP(2,1/((criteria_range1=criteria1)*(criteria_range2=criteria2)*(criteria_range3=criteria3)*(criteria_range4=criteria4)),output_range) etc

[CDNcameron]

I'll have to give it a try, what is the purpose of the 1/ and the * linking each criteria?


Tried it/Got it!

So the formula ended up being =LOOKUP(2,2/((E12:E27=E9)*(F12:F27=F9)),G12:G27)

For anyone else reading the 2/ is number of criteria and the * seperates each criteria.

Thanks Daff! Mama should be proud

[daffodil11]

Here's a good explanation of multiplying range/criteria against one another I put together for some of my colleagues.

I'd read them in order, to get a solid understanding.

1 Introduction to Arrays and True&False.xlsx

2 Introduction to Expert Lookup.xlsx