VLOOKUP and IFERROR

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:

Some videos you may like

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
Joined
Mar 7, 2013
Messages
146
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
Joined
Apr 27, 2011
Messages
49
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
Joined
Mar 7, 2013
Messages
146
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
Joined
Apr 27, 2011
Messages
49

ADVERTISEMENT

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
Joined
Mar 7, 2013
Messages
146
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
Joined
Apr 27, 2011
Messages
49

ADVERTISEMENT

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:

donjamin

New Member
Joined
Apr 27, 2011
Messages
49
The formula you typed contains an error and below just your standard response when you receive an error.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,922
Messages
5,525,645
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top