# VLOOKUP and IFERROR

#### donjamin

##### New Member
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

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### StefanVL

##### Board Regular
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);"")

#### donjamin

##### New Member
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:

#### StefanVL

##### Board Regular
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

#### donjamin

##### New Member

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.

#### StefanVL

##### Board Regular
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.

=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

#### donjamin

##### New Member

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:

#### StefanVL

##### Board Regular
What's the error you are receiving?

#### donjamin

##### New Member
The formula you typed contains an error and below just your standard response when you receive an error.

#### StefanVL

##### Board Regular
Might be cause you use commas instead of a semicolon

Replies
0
Views
183
Replies
4
Views
211
Replies
7
Views
273
Replies
2
Views
109
Replies
1
Views
107

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,485
Messages
5,831,962
Members
430,098
Latest member
bemmelen

### 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.

### Which adblocker are you using?

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

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