Excel CONCATENATE and VLOOKUP
Posted: 12 Nov 2012, 17:49
Hi all,
I am working with an Excel workbook with two sheets called ‘BMI’ and ‘BMIReport’.
The BMI sheet is from an insurance company, BMIReport is from our electronic medical record.
What I need to do is move the actual results of the BMI (Body Mass Index result) from BMIReport –to the new column "BMI Results"in the BMI sheet.
I know that I need to use VLOOKUP, but there is no unique identifier between the sheets– this is where my troubles begin.
One sheet, ‘BMI’ has LastName, FirstName and MI in ONE column
The other sheet, ‘BMIReport’ has the FirstName, LastName and MIDDLENAME listed out in 3 columns.
I first used “Text to Columns” on the BMI sheet to separate out the Name into 3 columns.
That worked fine – at least it looks as though it did.
Then, to create a unique identifier, I used =CONCATENATE(B2,C2,D2) in the BMIReport sheet. That works fine… but when I try it in the BMI sheet, it will not work –maybe because I separated out the name into the 3 columns?
I am new to these Excel functions – but have been learning a lot in researching – but I cannot make the connection regarding what might be wrong with what I’m doing.
Also, will VLOOKUP work on a concatenated field for what I am trying to do?
I have included a stripped down version if someone has time to take a look,
Thank you,
Vicky
I am working with an Excel workbook with two sheets called ‘BMI’ and ‘BMIReport’.
The BMI sheet is from an insurance company, BMIReport is from our electronic medical record.
What I need to do is move the actual results of the BMI (Body Mass Index result) from BMIReport –to the new column "BMI Results"in the BMI sheet.
I know that I need to use VLOOKUP, but there is no unique identifier between the sheets– this is where my troubles begin.
One sheet, ‘BMI’ has LastName, FirstName and MI in ONE column
The other sheet, ‘BMIReport’ has the FirstName, LastName and MIDDLENAME listed out in 3 columns.
I first used “Text to Columns” on the BMI sheet to separate out the Name into 3 columns.
That worked fine – at least it looks as though it did.
Then, to create a unique identifier, I used =CONCATENATE(B2,C2,D2) in the BMIReport sheet. That works fine… but when I try it in the BMI sheet, it will not work –maybe because I separated out the name into the 3 columns?
I am new to these Excel functions – but have been learning a lot in researching – but I cannot make the connection regarding what might be wrong with what I’m doing.
Also, will VLOOKUP work on a concatenated field for what I am trying to do?
I have included a stripped down version if someone has time to take a look,
Thank you,
Vicky