# Software

## Question

Locked

### Excel formula - copy cells from second worksheet

By ·
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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

Collapse -

### re: formula

by In reply to Excel formula - copy cell ...

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.

Collapse -

### vlookup/formula

by In reply to re: formula

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

Collapse -

### number/text/mix doesn't matter

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.

Collapse -

### Many thanks

by In reply to number/text/mix doesn't m ...

Brilliant, thank you both for your help :)

Collapse -

### sure

by In reply to Many thanks

||
||
||====
||====
||====
||====

Collapse -

## Related Discussions

• 2

ralwelby ·

• 1

Concho11 ·

• 3

#### eSports team management software

micagamer818 ·

• 3