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!

 

[StefanVL]

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.

If you keep it like that, the red reference will not change when you drag it down. If you want to be able to drag it down you either have to put it like this:

- Sheet1!$A2 (this will keep it from changing row)

or

- Sheet1!A2 (which will make it change if you drag it horizontally or vertically.

As for the table length, if it's always near 712 rows then you could put the marked part at
Sheet2!$A$2:$FI$750 and Sheet2!$EW$2:$EW$750 to be sure all records are included.

 

[donjamin]

I'd love to see what you're like when you shake off this rust.

 

[StefanVL]

I'd love to see what you're like when you shake off this rust.

This might be a nice article about absolute and relative formula references if you haven't learned about it before.

http://www.mrexcel.com/articles/relative-and-absolute-formulas.php