Question Locked

Excel formula - copy cells from second worksheet

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi, I hope someone can help. I've got a workbook that contains a daily sales log, and the second sheet is a stock list with prices.
In the sales log, Col. A is where the barcode data is entered (we've got that bit sorted) and Col. C is where the price comes up (hopefully).
In sheet 2, the barcode data is already entered in col. C, with the prices in Col.B.
What I need to happen is for any given cell in Col A in sheet 1, when a value is entered (barcode data) say in A3, I want it to search the whole of Col.C in sheet 2, then when it finds a match, say on line 17, I need it to copy the contents of B17 to C3 in sheet one.
I think thats as best as I can explain it.
Many thanks
Madison

7 replies

by thumbsup2

Use the VLOOKUP function in column C of sheet one to lookup values in sheet two. Just make sure that sheet two is sorted by the lookup column. If it's not sorted, the VLOOKUP function will stop searching when it reaches a value greater than the one it's searching for and fill in the last value that it found. Excel has built-in help for this function to tell you how to use it.

by madison

The data I'm searching is a mix of letters and numbers (10L41A6) will that make any difference?

by absolutely

if you type '=vlookup(' [without quotes] into a cell in column c you'll see the rest of the function's syntax appear nearby. To refer to Sheet2 in the formula, type 'Sheet2!'. Define the table_array as $A$1:$C$600 [or whatever number the last row is]. Otherwise, when you copy the function, the range you define will be "helpfully" adjusted to $A$2:$C$601 in the next cell, and so on, and the function will evaluate to #N/A!, even though the the lookup values do exist, in the range you initially defined.

by madison

Brilliant, thank you both for your help :)

This discussion is closed to new replies.