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

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

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

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

vlookup using range with catenated first and second column val
 

Best I can offer is to have a look at a simplified copy of the workbook so I
can see what is what


Include a copy of your posting in the email so I recall what you have
tried/are attempting.

--
Regards,
Tom Ogilvy


"klysell" wrote:

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