Formula to deliver answer that is in another sheet, to the left of a matched cell

0
votes

I am working on a formula that delivers text from a cell in another sheet, that is to the left of a matched cell. This is as far as I have got. 

=LOOKUP(A2,Sheet1!$A$1:$A$46729,Sheet1!$B$2:$B$46729)

In sheet 1 there is a selection of product data. There are product numbers in column A and there needs to be the correct barcode placed in each cell in column B. In sheet 2 there are also product codes in column A and barcodes in column B. However there are significantly more rows of data in Sheet 2.

What is needed is a formula to place in Sheet1!B2 that looks up Sheet1!A2 in Sheet2!A2:A50000. If A2 is matched at e.g. Sheet2!A90, then the result in Sheet1!B2 should be the value in Sheet2!B90. The specific issue I am having is getting the Sheet2!B90 in Sheet1!B2.

1
excel-formula
if you're using lookup function, make sure your search column is in ascending order. Second your formula needs to be referencing the correct sheet for the lookup arrays, so Sheet2 not Sheet1. – amoy

1 Answers

1
votes

You can use Index/Match:

=Index(Sheet2!$B$2:$B$46729,Match(A2,Sheet2!$A$2:$A$46729,0))

Or VLOOKUP:

=VLOOKUP(A2,Sheet2!$A$2:$B$46729,2,false)

The last parameters of the MATCH and VLOOKUP functions respectively are very important as it forces it to search for an exact value and not assume a sorted list. VLOOKUP would no longer work if you reversed columns A and B on Sheet2 because the lookup column has to be the first column in the lookup range.