Excel CONCATENATE and VLOOKUP

Excel CONCATENATE and VLOOKUP

Postby Spider » 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
You do not have the required permissions to view the files attached to this post.
Post=85200
Spider
StarLounger
 
Posts: 89
Joined: 11 Feb 2010, 21:59
Location: Ohio

Re: Excel CONCATENATE and VLOOKUP

Postby HansV » 12 Nov 2012, 17:59

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. :scratch:
Regards,
Hans
Post=85203
User avatar
HansV
Clever Clogs
 
Posts: 32812
Joined: 16 Jan 2010, 00:14
Location: Leiden, The Netherlands

Re: Excel CONCATENATE and VLOOKUP

Postby Spider » 12 Nov 2012, 19:56

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?
Post=85211
Spider
StarLounger
 
Posts: 89
Joined: 11 Feb 2010, 21:59
Location: Ohio

Re: Excel CONCATENATE and VLOOKUP

Postby HansV » 12 Nov 2012, 20:50

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
You do not have the required permissions to view the files attached to this post.
Regards,
Hans
Post=85214
User avatar
HansV
Clever Clogs
 
Posts: 32812
Joined: 16 Jan 2010, 00:14
Location: Leiden, The Netherlands

Re: Excel CONCATENATE and VLOOKUP

Postby Spider » 12 Nov 2012, 20:57

Thank you so much. I will work on this...
Post=85216
Spider
StarLounger
 
Posts: 89
Joined: 11 Feb 2010, 21:59
Location: Ohio


Return to Excel

Who is online

Users browsing this forum: CCBot [Bot] and 1 guest