# 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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### 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
37
Replies
2
Views
40
Replies
1
Views
136
Replies
3
Views
36
Replies
3
Views
101