1

I am trying to write a vlookup that pulls the amount from another sheet based on three lookup values.

the Vlookup formula needs to be in the amount cell:

State   ID   CITY               Amount
CO      3    Colarado spring    
AZ      69   Phoenix    
FL      97   Tampa

Here is where the amound should come from : range A1:D5

State   ID  City            Amount
CO      3   Colarado spring 10
AZ      69  Phoenix         36
FL      97  Tampa           11
John
  • 452
  • 3
  • 16

2 Answers2

2

Scott Craner's answer works great if the values are numbers. If the value to be returned is not numeric, or you don't want to sum up duplicates, you can use two other approaches:

Approach 1:

Create a helper column in the lookup table, and combine the three criteria columns into one with the formula

=A2&B2&C2

enter image description here

Now you can use a lookup formula in your original sheet 

=INDEX(data!D:D,MATCH(A2&B2&C2,data!E:E,0))

Copy down.

enter image description here

Approach 2:

If you don't want to create a helper column, the following formula can be used, but don't use this with whole columns (it will be very slow then).

=INDEX(data!$D$2:$D$5,MATCH(A2&B2&C2,INDEX(data!$A$2:$A$5&data!$B$2:$B$5&data!$C$2:$C$5,0),0))

enter image description here

teylyn
  • 34,374
  • 4
  • 53
  • 73
1

Use SUMIFS():

=SUMIFS(Sheet1!D:D,Sheet1!A:A,A2,Sheet1!B:B,B2,Sheet1!C:C,C2)

This works great if the combination of the three columns are unique and value returned is numeric.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81