vlookup - after exhaustive search
 

Hi! Searching for "vlookup multiple criteria" yielded answers suggesting
a method other than vlookup
I'd like to accomplish the following (obviously the syntax is an
abortion).
Givens are variable(v) 1 and v2. By this time in my program, they are
defined.
Goal is to lookup a qty to call v3
Something like:
v3=vlookup((sheet2:v1,tablecolumn1)
AND(sheet2:v2,tablecolumn2)),3)
The 3 at the end would say that v3=the value in the 3rd column.
My first column has multple entries matching v1 and are not grouped
together.
The second column then has only one entry where v2 exists in
conjunction with v1. I need to assign the value in column 3 to v3.
Can a pure vlookup statement handle this?
John


--
jgriffs
------------------------------------------------------------------------
jgriffs's Profile: http://www.excelforum.com/member.php...o&userid=30947
View this thread: http://www.excelforum.com/showthread...hreadid=508335

vlookup - after exhaustive search
 

You could always add another column which concatenates the values in v1 and
v2 and then use vlookup against that.

--
Tim Williams
Palo Alto, CA


"jgriffs" wrote in
message ...

Hi! Searching for "vlookup multiple criteria" yielded answers suggesting
a method other than vlookup
I'd like to accomplish the following (obviously the syntax is an
abortion).
Givens are variable(v) 1 and v2. By this time in my program, they are
defined.
Goal is to lookup a qty to call v3
Something like:
v3=vlookup((sheet2:v1,tablecolumn1)
AND(sheet2:v2,tablecolumn2)),3)
The 3 at the end would say that v3=the value in the 3rd column.
My first column has multple entries matching v1 and are not grouped
together.
The second column then has only one entry where v2 exists in
conjunction with v1. I need to assign the value in column 3 to v3.
Can a pure vlookup statement handle this?
John


--
jgriffs
------------------------------------------------------------------------
jgriffs's Profile:
http://www.excelforum.com/member.php...o&userid=30947
View this thread: http://www.excelforum.com/showthread...hreadid=508335

vlookup - after exhaustive search
 

Not using =vlookup().

You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

jgriffs wrote:

Hi! Searching for "vlookup multiple criteria" yielded answers suggesting
a method other than vlookup
I'd like to accomplish the following (obviously the syntax is an
abortion).
Givens are variable(v) 1 and v2. By this time in my program, they are
defined.
Goal is to lookup a qty to call v3
Something like:
v3=vlookup((sheet2:v1,tablecolumn1)
AND(sheet2:v2,tablecolumn2)),3)
The 3 at the end would say that v3=the value in the 3rd column.
My first column has multple entries matching v1 and are not grouped
together.
The second column then has only one entry where v2 exists in
conjunction with v1. I need to assign the value in column 3 to v3.
Can a pure vlookup statement handle this?
John

--
jgriffs
------------------------------------------------------------------------
jgriffs's Profile: http://www.excelforum.com/member.php...o&userid=30947
View this thread: http://www.excelforum.com/showthread...hreadid=508335

--

Dave Peterson