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.

Which function do I use?
 

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
...
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.

Which function do I use?
 

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
...
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.

Which function do I use?
 

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
...
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.

Which function do I use?
 

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.

Which function do I use?
 

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.

Which function do I use?
 

=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.

Which function do I use?
 

=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.