Which function do I use?

  • Thread starter Thread starter Zong
  • Start date Start date
Z

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.
 
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))
 
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!
 
They are formatted as text.
Is it going to cause a problem?
Click to expand...

As long as the lookup_value you enter in B2 is also formatted as TEXT then
there should be no problem.
 
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
 
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.
 
=LOOKUP(B2,Sheet2!B2:B6,Sheet2!A2:A6)

That requries the Mfg Model on Sheet2 be sorted in ascending order.
 
=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)
Click to expand...

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