Looking for a function that performs a special kind of Vlookup

  • Thread starter Thread starter Ayo
  • Start date Start date
A

Ayo

Let say you have a table with columns A, B, C, D, E, F and you want to do a
vlookup, but not the regular vlookup but one that takes into account values
in columns A and B. For instance:

A B C D E F
Day Tue 2 4 3 3
Day Wed 7 6 8 5
Day Thu 2 1 9 7

Taking the 2nd row, I want to do a vlookup based on Day and Wed instead of
just using Day. Is there a function like that or any combination of functions
that I can use to do this?
 
You can use sumproduct to do something close to what you want. It is similar
to a sumif with multiple criteria. It will sum all of the records where it
finds both Day and Wed. Auuming there is only 1 unique instance then you will
get back the number similar to a Vlookup

=sumproduct(--(A2:A10="Day"), --(B2:B10="Wed"), C2:C10)

will return 7...

Here is a link to sumproduct...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

The author of that page is Bob Phillips who regularily posts on this site.
If this post is helpful buy him a beer.
 
I put your data in A1:G3
Then I inserted a new column A
In A1 I used formula =B1&C1 (giving DayTue)
Copied formula down the column
In K1 (could be anywhere) I entered the text: Day and in L1 the text: Wed
In M1 I used formula =VLOOKUP(K1&L1,A1:G3,4,FALSE) which returned value 7 as
hoped for.

Then for fun, I hid column A and all worked well

best wishes
 
Thanks Jim. Works great.

Jim Thomlinson said:
You can use sumproduct to do something close to what you want. It is similar
to a sumif with multiple criteria. It will sum all of the records where it
finds both Day and Wed. Auuming there is only 1 unique instance then you will
get back the number similar to a Vlookup

=sumproduct(--(A2:A10="Day"), --(B2:B10="Wed"), C2:C10)

will return 7...

Here is a link to sumproduct...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

The author of that page is Bob Phillips who regularily posts on this site.
If this post is helpful buy him a beer.
Click to expand...
 
If per chance you were to want to return 7, 6, 8, 5 instead of just 7 you
could do this.

Select M, N, O, and P. While still selected type in Bernard's formula
modified like this.

=VLOOKUP(K1&L1,A1:G3,{4,5,6,7},FALSE)

Now commit with Ctrl + Shift + Enter, which is an Array Enter. Excel will
put { } around the formula and you will have 7 6 8 & 5 in the four cells.

DayThu returns 2 1 9 7 in the four cells.

If you want to modify the formula later to accomidate new data you will need
to select all four cells and make the modifications and array enter again.

HTH
Regards,
Howard
 
Back
Top