VLOOKUP and IFERROR

D

donjamin

New Member
Joined
Apr 27, 2011
Messages
49
Hello people,

I'm a bit of a newb when it comes to these formulas and was hoping someone could kindly help. I reckon it's probably something quite simple, so hopefully it's not too much to ask!

Basically, sheet 1 will have all of the data manually inputted, and will rely on sheet 2 (system data dump) to auto input corresponding data.

For instance:

On sheet 1, A2 will be a scanned barcode, and ideally B2 would automatically provide the company's product code related to that barcode (found on sheet 2). On Sheet 2 the scanned barcode would be found under column EW, and the corresponding product code under column AB. I will do a lot more with this sheet, but if someone could kindly provide this formula I think I could figure out the rest.

Thanks so much!
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Assuming that you would like to search the product name in sheet 1 based on the barcode;

=IFERROR(VLOOKUP(EW1;Sheet1!$A$2:$A$20;2;0);"")
 
Upvote 0
Thank you Stefan for the speedy response!

I've received an error trying to use that code.

So A1 in sheet 1 will be the EAN number (obtained using a scanner), which that same EAN would be found on sheet 2 in column EW. What I would like B1 on sheet 1 to display is that same EAN number's corresponding product code, found on sheet 2 column AB (which would be on the same row). Hope that makes sense!
 
Last edited:
Upvote 0
That makes it more clearer indeed.

I'd use index/match in this case because Vlookup cannot look back.

=IFERROR(INDEX(Sheet2!$AB$1:$EW$4;MATCH(Sheet1!$A$1;Sheet2!$EW$1:$EW$4;0);1);"")

In this case i assume that your table on sheet2 start at column AB and thats why there's a 1 for the column. If you use headers you could also use:

=IFERROR(INDEX(Sheet2!$A$2:$EW$4;MATCH(Sheet1!$A$1;Sheet2!$EW$2:$EW$4;0);MATCH("product code";Sheet2!A1:EW1;0));"")

All depends on how your large your table is tho, but it's a beginning :)
 
Upvote 0
Stefan that's amazing, I entered that formula in B1 (sheet 1) and received yet another error:(

The table on Sheet 2 starts at A and ends at FI, it's quite a big data dump file. I know I could make it easier on myself to remove unneccessary columns but as I'll be dumping the data on a regular basis this makes it a lot easier.
 
Upvote 0
donjamin said:
Stefan that's amazing, I entered that formula in B1 (sheet 1) and received yet another error:(

The table on Sheet 2 starts at A and ends at FI, it's quite a big data dump file. I know I could make it easier on myself to remove unneccessary columns but as I'll be dumping the data on a regular basis this makes it a lot easier.
Click to expand...

=IFERROR(INDEX(Sheet2!$A$2:$FI$4;MATCH(Sheet1!$A$1;Sheet2!$EW$2:$EW$4;0);MATCH("product code";Sheet2!$A$1:$F$I1;0));"")

This is just based on some sample data i used to recreate your concept.
It could be possible that your table does not begin with headers in row 1 but perhaps on row2
 
Upvote 0
I've got headers in all columns starting in row 1 on sheet 2, and I received another error with this formula. I really appreciate your help mate! I can not use row 1 on sheet 1 if that makes it easier!
 
Last edited:
Upvote 0
The formula you typed contains an error and below just your standard response when you receive an error.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
A simple parcel tracker
2
Replies
11
Views
601
Boboka
B
A
  • Question
Xlookup, Vlookup and/or VBA for finding, matching and inputting data to separate sheet
Replies
0
Views
513
AlfredDB
A
B
  • Solved
Barcode Scan result is errant in MS Excel, good data in Notepad/G Sheets
Replies
2
Views
2K
bmoreskimore
B
S
  • Question
listbox value only being recognised when it contains a letter
Replies
1
Views
316
salbo
S
S
  • Question
VBA Help- Need to create VBA Function from worksheet
Replies
9
Views
438
scotty1978
S

Forum statistics

Threads
1,214,622
Messages
6,120,572
Members
448,972
Latest member
Shantanu2024

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top