Which function do I use?

Workbook with 2 spreadsheets. I need to look up a part number in one column
and have it give me the value in another column on the same row.
PS# Mfg. PN
(func goes here) 39-MC (this is from the 2nd spreadsheet)


Part of 2nd spreadsheet
PS Item Mfg Model
1056620 TB8000-CATV-MB
1056542 0013903001
1056538 13904000
1058015 0040904010
1058333 39-MC

So I need the PS# in the 1st spreadsheet to come back with the correct PS
Item from the second spreadsheet. In this case 1058333.

Appreciate any help.

I see you have leading zeros in some of your part #s. Excel doesn't like
leading 0s. Are these entries formatted as TEXT?

Try this and see if it works:

=INDEX(Sheet2!A$2:A$100,MATCH(B2,Sheet2!B$2:B$100, 0))


--
Biff
Microsoft Excel MVP


"Zong" wrote in message
news
Workbook with 2 spreadsheets. I need to look up a part number in one
column
and have it give me the value in another column on the same row.
PS# Mfg. PN
(func goes here) 39-MC (this is from the 2nd spreadsheet)


Part of 2nd spreadsheet
PS Item Mfg Model
1056620 TB8000-CATV-MB
1056542 0013903001
1056538 13904000
1058015 0040904010
1058333 39-MC

So I need the PS# in the 1st spreadsheet to come back with the correct PS
Item from the second spreadsheet. In this case 1058333.

Appreciate any help.

They are formatted as text. Is it going to cause a problem? It is not a
number that I have control over.

The function worked perfectly. Thank you very much!

"T. Valko" wrote:

I see you have leading zeros in some of your part #s. Excel doesn't like
leading 0s. Are these entries formatted as TEXT?

Try this and see if it works:

=INDEX(Sheet2!A$2:A$100,MATCH(B2,Sheet2!B$2:B$100, 0))


--
Biff
Microsoft Excel MVP


"Zong" wrote in message
news
Workbook with 2 spreadsheets. I need to look up a part number in one
column
and have it give me the value in another column on the same row.
PS# Mfg. PN
(func goes here) 39-MC (this is from the 2nd spreadsheet)


Part of 2nd spreadsheet
PS Item Mfg Model
1056620 TB8000-CATV-MB
1056542 0013903001
1056538 13904000
1058015 0040904010
1058333 39-MC

So I need the PS# in the 1st spreadsheet to come back with the correct PS
Item from the second spreadsheet. In this case 1058333.

Appreciate any help.

They are formatted as text.
Is it going to cause a problem?

As long as the lookup_value you enter in B2 is also formatted as TEXT then
there should be no problem.

--
Biff
Microsoft Excel MVP


"Zong" wrote in message
...
They are formatted as text. Is it going to cause a problem? It is not a
number that I have control over.

The function worked perfectly. Thank you very much!

"T. Valko" wrote:

I see you have leading zeros in some of your part #s. Excel doesn't like
leading 0s. Are these entries formatted as TEXT?

Try this and see if it works:

=INDEX(Sheet2!A$2:A$100,MATCH(B2,Sheet2!B$2:B$100, 0))


--
Biff
Microsoft Excel MVP


"Zong" wrote in message
news
Workbook with 2 spreadsheets. I need to look up a part number in one
column
and have it give me the value in another column on the same row.
PS# Mfg. PN
(func goes here) 39-MC (this is from the 2nd spreadsheet)


Part of 2nd spreadsheet
PS Item Mfg Model
1056620 TB8000-CATV-MB
1056542 0013903001
1056538 13904000
1058015 0040904010
1058333 39-MC

So I need the PS# in the 1st spreadsheet to come back with the correct
PS
Item from the second spreadsheet. In this case 1058333.

Appreciate any help.

try this variation

=LOOKUP(B2,Sheet2!B2:B6,Sheet2!A2:A6)

however, Index and Match is a better choice.

--
Hope this is helpful

Appreciate that you provide your feedback by clicking the Yes button below
if this post have helped you.


Thank You

cheers, francis










"Zong" wrote:

Workbook with 2 spreadsheets. I need to look up a part number in one column
and have it give me the value in another column on the same row.
PS# Mfg. PN
(func goes here) 39-MC (this is from the 2nd spreadsheet)


Part of 2nd spreadsheet
PS Item Mfg Model
1056620 TB8000-CATV-MB
1056542 0013903001
1056538 13904000
1058015 0040904010
1058333 39-MC

So I need the PS# in the 1st spreadsheet to come back with the correct PS
Item from the second spreadsheet. In this case 1058333.

Appreciate any help.

Hi,

You can also use these approaches:

=OFFSET(Sheet2!A1,MATCH(B2,Sheet2!B2:B6),)
or the Array entered formula
=MAX((Sheet2!B2:B6=B2)*Sheet2!A2:A6)

in addition to those that you got from Valko and xlmate:
=INDEX(Sheet2!A2:A6,MATCH(B2,Sheet2!B2:B6)) (slightly modified to make it
shorter than OFFSET)
=LOOKUP(B2,Sheet2!B2:B6,Sheet2!A2:A6)


To make this an array you must enter it by pressing Shift+Ctrl+Enter not
Enter.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Zong" wrote:

Workbook with 2 spreadsheets. I need to look up a part number in one column
and have it give me the value in another column on the same row.
PS# Mfg. PN
(func goes here) 39-MC (this is from the 2nd spreadsheet)


Part of 2nd spreadsheet
PS Item Mfg Model
1056620 TB8000-CATV-MB
1056542 0013903001
1056538 13904000
1058015 0040904010
1058333 39-MC

So I need the PS# in the 1st spreadsheet to come back with the correct PS
Item from the second spreadsheet. In this case 1058333.

Appreciate any help.

=LOOKUP(B2,Sheet2!B2:B6,Sheet2!A2:A6)

That requries the Mfg Model on Sheet2 be sorted in ascending order.

--
Biff
Microsoft Excel MVP


"xlmate" wrote in message
...
try this variation

=LOOKUP(B2,Sheet2!B2:B6,Sheet2!A2:A6)

however, Index and Match is a better choice.

--
Hope this is helpful

Appreciate that you provide your feedback by clicking the Yes button below
if this post have helped you.


Thank You

cheers, francis










"Zong" wrote:

Workbook with 2 spreadsheets. I need to look up a part number in one
column
and have it give me the value in another column on the same row.
PS# Mfg. PN
(func goes here) 39-MC (this is from the 2nd spreadsheet)


Part of 2nd spreadsheet
PS Item Mfg Model
1056620 TB8000-CATV-MB
1056542 0013903001
1056538 13904000
1058015 0040904010
1058333 39-MC

So I need the PS# in the 1st spreadsheet to come back with the correct PS
Item from the second spreadsheet. In this case 1058333.

Appreciate any help.

=OFFSET(Sheet2!A1,MATCH(B2,Sheet2!B2:B6),)
=INDEX(Sheet2!A2:A6,MATCH(B2,Sheet2!B2:B6))
=LOOKUP(B2,Sheet2!B2:B6,Sheet2!A2:A6)

None of those will work *unless* Sheet2 B2:B6 is sorted in ascending order.

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

You can also use these approaches:

=OFFSET(Sheet2!A1,MATCH(B2,Sheet2!B2:B6),)
or the Array entered formula
=MAX((Sheet2!B2:B6=B2)*Sheet2!A2:A6)

in addition to those that you got from Valko and xlmate:
=INDEX(Sheet2!A2:A6,MATCH(B2,Sheet2!B2:B6)) (slightly modified to make it
shorter than OFFSET)
=LOOKUP(B2,Sheet2!B2:B6,Sheet2!A2:A6)


To make this an array you must enter it by pressing Shift+Ctrl+Enter not
Enter.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Zong" wrote:

Workbook with 2 spreadsheets. I need to look up a part number in one
column
and have it give me the value in another column on the same row.
PS# Mfg. PN
(func goes here) 39-MC (this is from the 2nd spreadsheet)


Part of 2nd spreadsheet
PS Item Mfg Model
1056620 TB8000-CATV-MB
1056542 0013903001
1056538 13904000
1058015 0040904010
1058333 39-MC

So I need the PS# in the 1st spreadsheet to come back with the correct PS
Item from the second spreadsheet. In this case 1058333.

Appreciate any help.