Which function do I use?

[Zong]

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.

 

[T. Valko]

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

 

[Zong]

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]

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.

 

[xlmate]

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

 

[Shane Devenshire]

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.

 

[T. Valko]

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

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

 

[T. Valko]

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