Lookup and match reference in other WS

[BravoBravoAu]

Hi guys,


Hopefully an easy one – I often struggle withtroubleshooting the lookup function of excel.




I have a workbook with two worksheets:

1. Employee details (WS1)
   
2. Bank deposits (WS2)

Each month, I need to paste in bank deposit information intoWS2. I would like to be able to displaythe employee surname in WS2 based on the matching employee ID listed in thebank deposits (7-digit number) against the employee ID recorded in WS1.


WS1

<tbody> [TD="width: 208, bgcolor: transparent"] Employee ID [/TD]
[TD="width: 208, bgcolor: transparent"] Name [/TD]
[TD="width: 208, bgcolor: transparent"] 1234567 [/TD]
[TD="width: 208, bgcolor: transparent"] Brown [/TD]
[TD="width: 208, bgcolor: transparent"] 2345678 [/TD]
[TD="width: 208, bgcolor: transparent"] Arnold [/TD]
</tbody>

WS2

<tbody> [TD="width: 104, bgcolor: transparent"] Date [/TD]
[TD="width: 104, bgcolor: transparent"] Description [/TD]
[TD="width: 104, bgcolor: transparent"] Debit [/TD]
[TD="width: 104, bgcolor: transparent"] Credit [/TD]
[TD="width: 104, bgcolor: transparent"] Balance [/TD]
[TD="width: 104, bgcolor: transparent"] Employee lookup [/TD]
[TD="width: 104, bgcolor: transparent"] 15 Jul 18 [/TD]
[TD="width: 104, bgcolor: transparent"] 2345678 [/TD]
[TD="width: 104, bgcolor: transparent"] [/TD]
[TD="width: 104, bgcolor: transparent"] $1,236.00 [/TD]
[TD="width: 104, bgcolor: transparent"] $2,157.25 [/TD]
[TD="width: 104, bgcolor: transparent"] [/TD]
[TD="width: 104, bgcolor: transparent"] 13 Jul 18 [/TD]
[TD="width: 104, bgcolor: transparent"] 1234567 [/TD]
[TD="width: 104, bgcolor: transparent"] [/TD]
[TD="width: 104, bgcolor: transparent"] $523.00 [/TD]
[TD="width: 104, bgcolor: transparent"] $921.25 [/TD]
[TD="width: 104, bgcolor: transparent"] [/TD]
</tbody>

So in WS2, I am trying to get F2 to display ‘Arnold’ and F3to display ‘Brown’. Obviously these aresimplified versions of WSs with hundreds of rows.


Help? Thanks inadvance.

 

[Aladin Akyurek]

In F2 of Bank Deposits enter and copy down:

=VLOOKUP(B2,'Employee details'!A:B,2,0)

 

[BravoBravoAu]

In F2 of Bank Deposits enter and copy down:=VLOOKUP(B2,'Employee details'!A:B,2,0)

Thanks @Aladin. I can replicate your solution and make it work, however in the 'live' data, I seem to have an issue which I hadn't mentioned resulting in a #N/A error.The Employee ID in WS1 Column A is derived from another WS. The formula is WS1 Column A obtains the seven digits from WS3 column C by virtue of =RIGHT(cell reference,7). While WS1 Column A is formatted as number, its not 'matching' against the same number in WS2 column B. Can you follow my ramblings?

 

[Aladin Akyurek]

Does this tweak help?

=VLOOKUP(B2&"",'Employee details'!A:B,2,0)

 

[BravoBravoAu]

Does this tweak help?

=VLOOKUP(B2&"",'Employee details'!A:B,2,0)

No - thats the same error.

 

[Aladin Akyurek]

No - thats the same error.

Try:

=VLOOKUP(B2+0,'Employee details'!A:B,2,0)

 

[Garry T]

Aladin- you helped me with the same problem in 2016. I thought the +0 option fixed my problem. I've since learned it doesn't all of the time. Most of the time it works but sometimes in doesn't. I've made sure the formatting is consistent, etc. I've taken lines where it worked and painted the format to entire columns, without success. It doesn't see to be a formatting issue. I've made sure the versions of Excel are the same. I can't think of any reason for VLOOKUP not to work. Do you have any other ideas? Thank you.

 

[Aladin Akyurek]

Aladin- you helped me with the same problem in 2016. I thought the +0 option fixed my problem. I've since learned it doesn't all of the time. Most of the time it works but sometimes in doesn't. I've made sure the formatting is consistent, etc. I've taken lines where it worked and painted the format to entire columns, without success. It doesn't see to be a formatting issue. I've made sure the versions of Excel are the same. I can't think of any reason for VLOOKUP not to work. Do you have any other ideas? Thank you.

=VLOOKUP(IF(ISNUMBER(B2+0),B2+0,B2),'Employee details'!A:B,2,0)

This tries the capture the following scenario:

B2 is available column A as either number or as text.

Care to post the VLOOKUP formula you struggle with yourself?

 

[Garry T]

[TABLE="width: 192"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD] A
Work Order Number[/TD]
[TD]WO[/TD]
[TD]D4[/TD]
[/TR]
[TR]
[TD="align: right"]4770526[/TD]
[TD="align: right"]4770526[/TD]
[TD="align: right"]0

[/TD]
[/TR]
</tbody>[/TABLE]
Columns are A, B & C. Formula in 3rd column (C1) is =IF(ISNA(vlookup(a1,b$1:b$1425,1,false)),0,vlookup(a1,b$1:b$1425,1,false))
Thank you!

 

[Aladin Akyurek]

[TABLE="width: 192"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD] A
Work Order Number[/TD]
[TD]WO[/TD]
[TD]D4[/TD]
[/TR]
[TR]
[TD="align: right"]4770526[/TD]
[TD="align: right"]4770526[/TD]
[TD="align: right"]0

[/TD]
[/TR]
</tbody>[/TABLE]
Columns are A, B & C. Formula in 3rd column (C1) is =IF(ISNA(vlookup(a1,b$1:b$1425,1,false)),0,vlookup(a1,b$1:b$1425,1,false))
Thank you!

The formula you have should return 4770526, not 0.

1. Check whether Calculation Options are set to Automatic.

2. If you get #N/A (therefore 0) for A1 and A1 is a true number, then b$1:b$1425 must have it as text number. Then the following should succeed:

=VLOOKUP(A1&"",B$1:B$1425,1,FALSE)

3. If you get #N/A (therefore 0) for A1 and A1 is a text number, then b$1:b$1425 must have it as true number. Then the following should succeed:

=VLOOKUP(A1+0,B$1:B$1425,1,FALSE)

4. The following considers both possibilities:

=IFERROR(VLOOKUP(A1&"",B$1:B$1425,1,FALSE),IFERROR(VLOOKUP(A1+0,B$1:B$1425,1,FALSE),"not found"))