Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Urgent Dynamic Range with Vlookup
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 -- Regards, Jeff |
#2
|
|||
|
|||
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. Jeff wrote: 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 -- Regards, Jeff -- Dave Peterson |
#3
|
|||
|
|||
Hi Dave,
How would you write a VBA procedure to run this function on the whole column? -- Regards, Jeff "Dave Peterson" wrote: 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. Jeff wrote: 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 -- Regards, Jeff -- Dave Peterson |
#4
|
|||
|
|||
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=othershee t!$b$1:$b$100),0))") (watch out for typos) Jeff wrote: Hi Dave, How would you write a VBA procedure to run this function on the whole column? -- Regards, Jeff "Dave Peterson" wrote: 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. Jeff wrote: 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 -- Regards, Jeff -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic named range across multiple sheets | Excel Discussion (Misc queries) | |||
Dynamic Range Chart ??? | Charts and Charting in Excel | |||
how to change range for dynamic chart in excel 2000 with button? | Charts and Charting in Excel | |||
dynamic range for excel chart | Excel Discussion (Misc queries) | |||
How do I use Range Names listed in a VLookup table in a formula? | Excel Worksheet Functions |