We help IT Professionals succeed at work.

V-Lookup

jspc
jspc asked
on
97 Views
Last Modified: 2017-03-23
Hello,

I'm wondering if someone can please help me with a VLOOKUP in an Excel File?

I have 2 datasheets. Stock and Datasource.

I'm trying to reference the Sheet Stock Column J to the Sheet Datasource column b

Please see attached example.
STOCKFINAL.xlsx
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
Hi,

pls try
=VLOOKUP(A2,A1Range,2,0)

Open in new window

the first 2 cells in Datasource are text. pls convert to number to work

Regards

Author

Commented:
I'm getting #N/A

I have converted to number
CERTIFIED EXPERT
Top Expert 2016

Commented:
Na where is no corresponding reference
STOCKFINALv1.xlsx
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
For convenience of writing the formula I have converted the lookup table to a Table.

The formula is then =VLOOKUP(A2,Table1[#All],2,0)

However, your codes do not seem to match. See the result when I type in a dummy code - Test1
STOCKFIN-1-AL.xlsx
CERTIFIED EXPERT
Top Expert 2016
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
I have added IFERROR to show if a code is missing.

Author

Commented:
Thank you, however if I copy your change down it doesn't reference the barcode number in the Datasource sheet
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
Forgot the last attachment
STOCKFIN-1-AL.xlsx

Author

Commented:
Thank you
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Even this question is closed. Another approach would be to use:
=IFERROR(INDEX(DataSource!B:B,MATCH(A2,DataSource!A:A)),"")

Open in new window

Please find attached...
STOCKFINAL_v1.xlsx
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
The codes are definitely different, see the effect of matching codes from the main table with the Lookup Table, cells highlighted in yellow, the formula works
STOCKFIN-1-AL.xlsx