|
VLOOKUP --- Assistance Needed
Morning everyone, Is it possible to use vlookup by trimming values in a column. It's a little hard to explain, but here's an example: Worksheet 1: column1 column2 --------- --------- hha1xxx "matched value from worksheet 2" -------------------------------------------------------------------------- Worksheet 2: colum1 column2 --------- --------- hha2xxx 845 --------------------- I want to match by hha2xxx by extracting "hha"&2&"xxx" in worksheet1. Is this possible? Here's what the result should be: Worksheet 1: column1 column2 --------- --------- hha1xxx 845 ** Please advise. -- Thanks, Himansu |
VLOOKUP --- Assistance Needed
Did you try it?
This worked for me =VLOOKUP(LEFT(A1,3)&"2xxx",Sheet2!A1:B10,2,FALSE) As did =VLOOKUP(LEFT(A1,3)&J1&"xxx",Sheet2!A1:B10,2,FALSE ) with value of 2 in J1 best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Himansu" wrote in message ... Morning everyone, Is it possible to use vlookup by trimming values in a column. It's a little hard to explain, but here's an example: Worksheet 1: column1 column2 --------- --------- hha1xxx "matched value from worksheet 2" -------------------------------------------------------------------------- Worksheet 2: colum1 column2 --------- --------- hha2xxx 845 --------------------- I want to match by hha2xxx by extracting "hha"&2&"xxx" in worksheet1. Is this possible? Here's what the result should be: Worksheet 1: column1 column2 --------- --------- hha1xxx 845 ** Please advise. -- Thanks, Himansu |
VLOOKUP --- Assistance Needed
Hello Bernard, This was th eformula I was thinking of: vlookup by Left(A15,4)&"2"&Right(a15,len(a15)-5) to get key count for Final Mail Quantity 2 (new column N) ** How can I embed this into a vlookup formula? "Bernard Liengme" wrote in message ... Did you try it? This worked for me =VLOOKUP(LEFT(A1,3)&"2xxx",Sheet2!A1:B10,2,FALSE) As did =VLOOKUP(LEFT(A1,3)&J1&"xxx",Sheet2!A1:B10,2,FALSE ) with value of 2 in J1 best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Himansu" wrote in message ... Morning everyone, Is it possible to use vlookup by trimming values in a column. It's a little hard to explain, but here's an example: Worksheet 1: column1 column2 --------- --------- hha1xxx "matched value from worksheet 2" -------------------------------------------------------------------------- Worksheet 2: colum1 column2 --------- --------- hha2xxx 845 --------------------- I want to match by hha2xxx by extracting "hha"&2&"xxx" in worksheet1. Is this possible? Here's what the result should be: Worksheet 1: column1 column2 --------- --------- hha1xxx 845 ** Please advise. -- Thanks, Himansu |
VLOOKUP --- Assistance Needed
This worked form me:
=VLOOKUP(LEFT(A15,3)&"2"&RIGHT(A15,LEN(A15)-4),Sheet2!A10:B16,2,FALSE) note your 4-3, and your 5 -4 Do you know how to use Evaluate Formula to see how a formula 'evolves' best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Himansu" wrote in message ... Hello Bernard, This was th eformula I was thinking of: vlookup by Left(A15,4)&"2"&Right(a15,len(a15)-5) to get key count for Final Mail Quantity 2 (new column N) ** How can I embed this into a vlookup formula? "Bernard Liengme" wrote in message ... Did you try it? This worked for me =VLOOKUP(LEFT(A1,3)&"2xxx",Sheet2!A1:B10,2,FALSE) As did =VLOOKUP(LEFT(A1,3)&J1&"xxx",Sheet2!A1:B10,2,FALSE ) with value of 2 in J1 best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Himansu" wrote in message ... Morning everyone, Is it possible to use vlookup by trimming values in a column. It's a little hard to explain, but here's an example: Worksheet 1: column1 column2 --------- --------- hha1xxx "matched value from worksheet 2" -------------------------------------------------------------------------- Worksheet 2: colum1 column2 --------- --------- hha2xxx 845 --------------------- I want to match by hha2xxx by extracting "hha"&2&"xxx" in worksheet1. Is this possible? Here's what the result should be: Worksheet 1: column1 column2 --------- --------- hha1xxx 845 ** Please advise. -- Thanks, Himansu |
| All times are GMT +1. The time now is 07:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com