V-Lookup

jspc
jspc used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Top Expert 2016

Commented:
Na where is no corresponding reference
STOCKFINALv1.xlsx
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Roy CoxGroup Finance Manager

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
Top Expert 2016
Commented:
if you want you could use
=IFERROR(VLOOKUP(A2,A1Range,2,0),"")
Roy CoxGroup Finance Manager

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

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

Author

Commented:
Thank you
ShumsManaging Director/Excel VBA Developer
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

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial