Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup using range with catenated first and second column values
Hi,
I have a vlookup fuction - e.g. (VLOOKUP($C$3,_Map1,8,FALSE) - that needs to lookup the value (in the 8th position) in $C$3 (current sheet) that is equal to the concatenated value of the first and second column values in my range, "_Map1", which is contained on my "Summary" sheet. For example, my value for $C$3 is "Lysell, Kent(17)". I need to lookup up the particular row in my range where column c value is "Lysell, Kent" and the adjacent column d value is "17" ("17" in column d doesn't have "()" around it. I've seen other solutions on this DG, but these are using the "match" function. Is this solution adaptable to my "vlookup" function? Or should I change all my formulas to the "match" fn? Thanks in advance! -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.943.9098 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup using range with catenated first and second column values
Match gives you the row. You then use that with Index to return the value
you want =Index(column with return values,Match(set up to do your match),1) Entered with Ctrl+Shift+enter since this is an array formula. -- regards, Tom Ogilvy "klysell" wrote: Hi, I have a vlookup fuction - e.g. (VLOOKUP($C$3,_Map1,8,FALSE) - that needs to lookup the value (in the 8th position) in $C$3 (current sheet) that is equal to the concatenated value of the first and second column values in my range, "_Map1", which is contained on my "Summary" sheet. For example, my value for $C$3 is "Lysell, Kent(17)". I need to lookup up the particular row in my range where column c value is "Lysell, Kent" and the adjacent column d value is "17" ("17" in column d doesn't have "()" around it. I've seen other solutions on this DG, but these are using the "match" function. Is this solution adaptable to my "vlookup" function? Or should I change all my formulas to the "match" fn? Thanks in advance! -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.943.9098 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup using range with catenated first and second column val
Thanks Tom!
-- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.943.9098 "Tom Ogilvy" wrote: Match gives you the row. You then use that with Index to return the value you want =Index(column with return values,Match(set up to do your match),1) Entered with Ctrl+Shift+enter since this is an array formula. -- regards, Tom Ogilvy "klysell" wrote: Hi, I have a vlookup fuction - e.g. (VLOOKUP($C$3,_Map1,8,FALSE) - that needs to lookup the value (in the 8th position) in $C$3 (current sheet) that is equal to the concatenated value of the first and second column values in my range, "_Map1", which is contained on my "Summary" sheet. For example, my value for $C$3 is "Lysell, Kent(17)". I need to lookup up the particular row in my range where column c value is "Lysell, Kent" and the adjacent column d value is "17" ("17" in column d doesn't have "()" around it. I've seen other solutions on this DG, but these are using the "match" function. Is this solution adaptable to my "vlookup" function? Or should I change all my formulas to the "match" fn? Thanks in advance! -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.943.9098 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup using range with catenated first and second column values
Hi Tom,
Here's my formula: {=INDEX(_Map1,MATCH($C$3,NameRange&"("&AgreementRa nge&")",1),3)} My formula doesn't seem to be working. My value in $c$3 is "lysell, kent(17)", and this is value I'm using to lookup the row where this value is found (on the Summary sheet) in the "_Map1" range. However, I get this lookup value by catenating "lysell, kent" in column c with "17" in column d (including concatenating "(" and ")" around the AgreementRange). I would like the 3rd column in my "_Map1" range. I've named the employee names in column c "NameRange" and the agreement numbers in column d "AgreementRange". Any help would be immensely appreciated! Thanks in advance, -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.943.9098 "klysell" wrote: Hi, I have a vlookup fuction - e.g. (VLOOKUP($C$3,_Map1,8,FALSE) - that needs to lookup the value (in the 8th position) in $C$3 (current sheet) that is equal to the concatenated value of the first and second column values in my range, "_Map1", which is contained on my "Summary" sheet. For example, my value for $C$3 is "Lysell, Kent(17)". I need to lookup up the particular row in my range where column c value is "Lysell, Kent" and the adjacent column d value is "17" ("17" in column d doesn't have "()" around it. I've seen other solutions on this DG, but these are using the "match" function. Is this solution adaptable to my "vlookup" function? Or should I change all my formulas to the "match" fn? Thanks in advance! -- Kent Lysell Financial Consultant Ottawa, Ontario W: 613.943.9098 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup using range with catenated first and second column val
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate values in an unlimited range of cells in a column WITHOUTalso calculating values that had previously been filtered AGAINST? | Excel Discussion (Misc queries) | |||
Count Unique Values in 1 Column based on Date Range in another Column | Excel Worksheet Functions | |||
index, match lookup using catenated values not working | Excel Worksheet Functions | |||
Programatically Naming Worksheets (using catenated values) | Excel Programming | |||
Vlookup with a range of values | Excel Discussion (Misc queries) |