Using "INDEX/MATCH" to reference an existing external workbook to return Item# description and price

  • Thread starter Thread starter HuffieVA
  • Start date Start date
H

HuffieVA

Although it seems like a simple task, its really getting the best of
me...

I have a price list with: ITEM#,DISCRIPTION,UNIT SIZE(i.e. square
feet), and cost in column's A thru D
Iwant to refrence the information by partnumber from another
woorkbook, and have the information inserted into the new workbook,
somewhat like an invoice or an estimate, could one of you kind folks
give me a hand with this ?

Thanks in advance...


Rodd
 
Say in Sheet2 - A1:D6 you have:

ITEM# Description UnitSize Cost
321 Product1 Sq Ft 35.00
222 Product2 Gallons 22.15
111 Product3 Pallet 35.89
255 Product4 Drum 1.15
521 Product5 Gallons 10.00

At the Menu, Insert, Name, Define... In Name Box type-in MyList
In the Refers to: Box (at the bottom) enter =Sheet2!$A$2:$D$6;
then Click Add, then Close.

In Sheet1 you can reference in Description, UnitSize or Cost
as follows: B5 is an example of referencing the Description

In Cell A5 enter 222 in Cell B5 enter
=VLOOKUP($A5,MyList,2,FALSE)

Copy B5 to C5 and D5 (Changing the 3rd Argument in C5 to 3 and in D5 to 4.

HTH
 
book one has all your date

on your new book in a1 type the part number
in b2 use this formula

=INDEX([Book1]Sheet1!$A$1:$D$6, MATCH(A1,[Book1]Sheet1!$A$1:$A$6,),
MATCH("description",[Book1]Sheet1!$A$1:$D$1,))

replace book1 with the actually name of the spreadsheet and sheet1
with the actual name of the sheet

replace description with the actual name of your header
now if you change the part number in a1 you will see that the info in
b2 changes for you

you can use this formula in any cell just reference the cell with the
part number and change the description to what ever header you want to
retrieve

hope this helps

Randall
 
Back
Top