Excel CONCATENATE and VLOOKUP

[Spider]

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

[HansV]

How exactly did you arrive at the LastName, FirstName and MI parts on the BMI sheet? The full name in row 2 is "AD, BRENDA G" but there is no MI, and the full name in row 3 is "ADKI, JANE" but you have "P" in MI.

[Spider]

In the sheet BMIReport - Brenda has no MI recorded -it was not entered into the electronic medical record -so I stripped the MIs out of the "BMI" full name and am not using them at all. Sheet BMI actually calls for the full middle name - but sometimes only the initial is entered. There was too much confusion to use MI since the data is coming from two different applications. I hope that answers your question?

[HansV]

I see two problems:
- BMI!B2 has a space after the name. You should try to avoid that.
- BMI column A is formatted as text, so formulas won't work.
If you remove the spaces and format BMI column A as general, then enter the formulas again, they work.
To avoid blank BMIs returning 0 in the VLOOKUP formula, you can use

=IF(VLOOKUP($A2,BMIReport!A:G,7,FALSE)="","",VLOOKUP($A2,BMIReport!A:G,7,FALSE))

See attached version.

BMI_eileens.xls

[Spider]

Thank you so much. I will work on this...