I want a Vlookup function that looks for a range of items in another
worksheet and if yes returns the part number and cost. See attached
file

For example Worksheet AC-P & HP-P are the target sheets. Looking at
worksheet AC-P cell A9 if the cell contents of A9 is CKL18-1* then a
function in I9 LOOKS AT worksheet PRODUCTS for a CKL18-1* and writes
the items prod number in that cell A9. I'd also like it to do the same
for the ARUF018-00*-1* that is in the next column and so on.


+-------------------------------------------------------------------+
|Filename: GOODMAN.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4393 |
+-------------------------------------------------------------------+

--
nander
------------------------------------------------------------------------
nander's Profile: http://www.excelforum.com/member.php...fo&userid=6156
View this thread: http://www.excelforum.com/showthread...hreadid=515764

Hi nander

The syntax for VLOOKUP is =VLOOKUP(<where to get criteria,<Range to
search,<column offset, <True or FALSE is optional). If you use false, you
will look for an exact match. If omitted, you will get the nearest match. I
do not think anybody will open your file though. Best to repost and give
details of your file setup.

If you have all your parts info in a contiguous range, nicely sorted by part
number, giving all relevant info such as part number, description, location,
cost and selling price in Cols A to E, and say you have named this range
Stock, you can use VLOOKUP to extract data from it. On your invoice, or
whatever doc you are using, say in cell A5 you enter a part number. In cell
B5 you would then have =VLOOKUP(AA5,[Products.xls]Sheet1!Stock,2,FALSE) to
extract the description. Changing the 2 to a 3 will give you location, 4 the
cost and 5 the selling price. If this is not what you are looking for, I
suggest you repost with detailed info.

hth

"nander" wrote:


I want a Vlookup function that looks for a range of items in another
worksheet and if yes returns the part number and cost. See attached
file

For example Worksheet AC-P & HP-P are the target sheets. Looking at
worksheet AC-P cell A9 if the cell contents of A9 is CKL18-1* then a
function in I9 LOOKS AT worksheet PRODUCTS for a CKL18-1* and writes
the items prod number in that cell A9. I'd also like it to do the same
for the ARUF018-00*-1* that is in the next column and so on.


+-------------------------------------------------------------------+
|Filename: GOODMAN.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4393 |
+-------------------------------------------------------------------+

--
nander
------------------------------------------------------------------------
nander's Profile: http://www.excelforum.com/member.php...fo&userid=6156
View this thread: http://www.excelforum.com/showthread...hreadid=515764