Vlookup from a vlookup

[Bryony309]

Hello,
I'm not sure if this is even possible but let's see.
I have an 11 digit reference number. Staff input the last five (as the first 6 as the same for everyone) when recording data in sheet 1 column C.
Sheet 2 then contains the five digit number, the full 11 digits and also the name attached to that reference.
Sheet 1 has a vlookup in column D which takes the five digits inputted and returns the full name attached from sheet 2.
However this is currently failing as sheet in sheet 2, column B contains a trim formula to get the five digits from column C's 11. D contains the full name.

I have attached the document to make it clearer.

I need to pull the full name from sheet 2 into sheet 1 by using a trimmed version of the reference.
I cannot use the values or copy and paste the trimmed values as the information in sheet 2 changes frequently.

Thank you

[AliGW]

Try this:

=VLOOKUP(C2,Sheet2!$B$2:$D$25,3,0)

[Bryony309]

Tried it before, doesn't work. Comes back with #N/A

[AliGW]

It works on your workbook here!

Excel 2016 (Windows) 32 bit

	C	D
2	27484	Frank Smith 7
3	28220	Frank Smith 8
4	28221	Frank Smith 9
5	28375	Frank Smith 10
6	28491	Frank Smith 11
7	28498	Frank Smith 12
8	28952	Frank Smith 13
9	29132	Frank Smith 14

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	D
2	=VLOOKUP(C2,Sheet2!$B$2:$D$25,3,0)

Sheet: Sheet1

[Bryony309]

It isn't on mine.
I'll try it on the real version

[AliGW]

Then attach your workbook with the formula in place so I can troubleshoot it.

[Bryony309]

I can't, due to data protection, hence i made a fake replica.

[AliGW]

So your replica is not of any use, since there is something fundamentally different about it.

Try this:

=VLOOKUP(VALUE(C2),Sheet2!$B$2:$D$25,3,0)

[Bryony309]

I cannot attach as it has information that cannot be share.
The replica is exactly the same just a new document, data is in the same place.
If the formula isn't working on the replica it won't work anyway.

[AliGW]

Have you tried the second formula I suggested?

Bryony - you need to understand that I am trying to help you. There WILL be something fundamentally different between your real data and the sample data (possibly in the set-up of the workbook itself) that means that the suggested formula works on the sample sheet but not on the real data. If you want help, then please work with us, not against us. I understand the reasons why you cannot share the real workbook.

[Bryony309]

I am trying it now.
I am using 2013 not 2016.
I do understand that I didn't say you were not.
I am not "working against you", I merely said your first suggestion didn't work on my replica.

[AliGW]

Just a quick query - do you have automatic calculation turned off?

[AliGW]

I am using 2013 not 2016.

Your profile says 2010 - please update it accordingly.

[AliGW]

Attached is your replica workbook with the formula I first suggested working. If it is not working at your end, then something is different about your set-up (not the version - 2013 v. 2016 will make no difference here).

[AliGW]

Does my copy of the sample workbook work correctly at your end?