VLOOKUP and IFERROR

[donjamin]

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!

 

[donjamin]

Says now I've generated too many arguments for this function.

 

[StefanVL]

Says now I've generated too many arguments for this function.

Could you copy the formula you entered?

 

[donjamin]

=IFERROR(INDEX(Sheet2!$A$2:$FI$4,0),MATCH(Sheet1!$A$2,Sheet2!$EW$2:$EW$4,0),MATCH("RefNo",Sheet2!$A$1:$FI$1,0)),"")

 

[StefanVL]

=IFERROR(INDEX(Sheet2!$A$2:$FI$4,0),MATCH(Sheet1!$A$2,Sheet2!$EW$2:$EW$4,0),MATCH("RefNo",Sheet2!$A$1:$FI$1,0)),"")

=IFERROR(INDEX(Sheet2!$A$2:$FI$4,0),MATCH(Sheet1!$A$2,Sheet2!$EW$2:$EW$4,0),MATCH("RefNo",Sheet2!$A$1:$FI$1,0)),"")

Your index is incorrect:

INDEX(Sheet2!$A$2:$FI$4,0) should be INDEX(Sheet2!$A$2:$FI$4,

being in total:=IFERROR(INDEX(Sheet2!$A$2:$FI$4,MATCH(Sheet1!$A$2,Sheet2!$EW$2:$EW$4,0),MATCH("RefNo",Sheet2!$A$1:$FI$1,0)),"")

 

[donjamin]

Stefan you're a beast!!

 

[StefanVL]

Stefan you're a beast!!

Bit rusty but glad i could help.

 

[donjamin]

Seriously appreciate this mate, and apologies for taking up your morning!

 

[donjamin]

Only issue I've found with this is I can't drag and drop the formula with it adjusting what cell it is trying to match. Arggggh!

 

[StefanVL]

Only issue I've found with this is I can't drag and drop the formula with it adjusting what cell it is trying to match. Arggggh!

That's probably because the formula i gave you has just a small array in which it operates. Meaning you have to enlarge the field to make it operate on larger scale. I don't know how many records but let's say it's 1000 records instead of just 2, which we used in the example. The only thing you'll have to change is the arrays i marked for you

=IFERROR(INDEX(Sheet2!$A$2:$FI$4,MATCH(Sheet1!$A$2,Sheet2!$EW$2:$EW$4,0),MATCH("RefNo",Sheet2!$A$1:$FI$1,0)),"")

These could be
Sheet2!$A$2:$FI$1001 and Sheet2!$EW$2:$EW$1001

All depends on the scale of your table.

 

[donjamin]

I just sat here and adjusted the red bit as that is what is being scanned, and thus searched for on Sheet 2.

=IFERROR(INDEX(Sheet2!$A$2:$FI$4,MATCH(Sheet1!$A$2,Sheet2!$EW$2:$EW$4,0),MATCH("RefNo",Sheet2!$A$1:$FI$1,0)),"")

for the first 100 cells. The table itself only has 712 rows.

Sorry edited as I selected wrong section.