Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP - search key is more than once available | Excel Worksheet Functions | |||
Need help with a search function - vlookup or ??? | New Users to Excel | |||
vlookup with search? | Excel Worksheet Functions | |||
Can VLOOKUP be used to search for more than one possible value? | Excel Discussion (Misc queries) | |||
vlookup & search | Excel Programming |