Question

Locked

Excel formula - copy cells from second worksheet

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

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

re: formula

by ThumbsUp2 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 madison 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

by Absolutely In reply to vlookup/formula

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 madison In reply to number/text/mix doesn't m ...

Brilliant, thank you both for your help :)

Collapse -

sure

by Absolutely In reply to Many thanks

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

Collapse -

Marked as helpful

by OnTheRopes In reply to sure

<img src="http://i201.photobucket.com/albums/aa12/sharewhat2/thumbs-up.jpg" >

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums