Urgent Dynamic Range with Vlookup

J

Jeff

I urgently need to define a formula that execute the following:

I need to do a vlookup on Value 1 to return in the value in column "C" ONLY
AND ONLY IF the value in column "B" is "SP". Then I need to do a vlookup on
Value 1 to return the value in column "C" ONLY ANLY IF the value in column
"B" is "ZA"



Value 1 SP 1000035
Value 1 ZA 1000036
 
D

Dave Peterson

If I understand correctly, you just want to match both column A and column B and
return the value in column C for the first row that matches A:B.

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

or

=index(othersheet!$c$1:$c$100,
match(1,("Value 1"=othersheet!$a$1:$a$100)*("SP"=othersheet!$b$1:$b$100),0))

(one cell)

These are both array formulas. 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.
 
J

Jeff

Hi Dave,

How would you write a VBA procedure to run this function on the whole column?

--
Regards,
Jeff



Dave Peterson said:
If I understand correctly, you just want to match both column A and column B and
return the value in column C for the first row that matches A:B.

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

or

=index(othersheet!$c$1:$c$100,
match(1,("Value 1"=othersheet!$a$1:$a$100)*("SP"=othersheet!$b$1:$b$100),0))

(one cell)

These are both array formulas. 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.
Click to expand...
 
D

Dave Peterson

I wouldn't.

Are you really using all 65536 rows? If you are, that's pretty amazing!

If you aren't, you could wrap application.evaluate around the expression:

dim res as variant
res = application.evaluate("index(othersheet!$c$1:$c$100," & _
"match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))")

(watch out for typos)

Hi Dave,

How would you write a VBA procedure to run this function on the whole column?
Click to expand...
 
Top