vlookup - after exhaustive search

[jgriffs]

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

 

[Tim Williams]

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

 

[Dave Peterson]

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.

 

[Guest]

Also see

http://xldynamic.com/source/xld.SUMPRODUCT.html