Looking up similar values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Need a statement that looks up a part # on one spreadsheet, the similar part # on another spreadsheet and pulls the price from the second spreadsheet. The only problem is that on the first spreadsheet the Part # for example is X012345 and on the second spreadsheet it is x012345AAB01. It's the same part number, just at a different level and I don't care about that. I need to make sure both part numbers exist on both spreadsheets so that I can pull the price from the second one

Can this be done??
 
One way using OFFSET(..MATCH()..)

Assume List is in Sheet1
(cols A and B, data in row2 down)
-------------

Part#...Price
1234......30
2345......50
etc

In Sheet2
---------
Part#'s are in col A, row2 down

Put in B2:

=OFFSET(Sheet1!$A$1,MATCH(A2,Sheet1!$A:$A,0)-1,1)

or with an error trap for non matching cases
(phrase "No match" returned instead of #NA)

=IF(ISNA(MATCH(A2,Sheet1!$A:$A,0)),"No match",OFFSET
(Sheet1!$A$1,MATCH(A2,Sheet1!$A:$A,0)-1,1))

Col B will return the prices for the part#s in col A
from the List in Sheet1
--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---

Frustrated Worker said:
Need a statement that looks up a part # on one
Click to expand...
spreadsheet, the similar part # on another spreadsheet and
pulls the price from the second spreadsheet. The only
problem is that on the first spreadsheet the Part # for
example is X012345 and on the second spreadsheet it is
x012345AAB01. It's the same part number, just at a
different level and I don't care about that. I need to
make sure both part numbers exist on both spreadsheets so
that I can pull the price from the second one.
 
oops, omitted mention of the "implicit" line:

Copy B2 down as many rows as there are part#'s in col A
 
Thanks for the response, but the real problem I'm having is taking a part # from Sheet 1, like X01234, and matching it with a part #, like 1234AA01, on sheet 2. They are both the same part #'s. Is there anyway I can do what I asked in my first question??

Thanks for the help
 
Sorry, mis-read the details earlier

If the examples in your original post are representative, viz ..
.. on the first spreadsheet the Part # for example is X012345
.. on the second spreadsheet it is x012345AAB01..
Click to expand...

maybe try this slightly revised ..

Assume the list is in Sheet2 as below
(cols A and B, data in row2 down)
and Part#s in col A are unique
-------------

Part#.......................Price
x012345AAB01......30
x123456BBC09----50
x234567DDF10......80
etc

In Sheet1
---------
Part#...................Price
x123456
x012345
x234567
etc

Put in B2:

=OFFSET(Sheet2!$A$1,MATCH("*"&A2&"*",Sheet2!A:A,0)-1,1)

or, with an error trap for non matching cases
(phrase "No match" returned instead of #NA)

=IF(ISNA(MATCH("*"&A2&"*",Sheet2!A:A,0)),"No
match",OFFSET(Sheet2!$A$1,MATCH("*"&A2&"*",Sheet2!A:A,0)-1,1))

Copy B2 down as many rows as there is data in col A

Col B will return the prices for the part#s in col A
from the List in Sheet2

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---

Frustrated Worker said:
Thanks for the response, but the real problem I'm having is taking a part
Click to expand...
# from Sheet 1, like X01234, and matching it with a part #, like 1234AA01,
on sheet 2. They are both the same part #'s. Is there anyway I can do what
I asked in my first question???
 
Back
Top