| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| ISBLANK function not working when cell is blank dut to function re | Excel Discussion (Misc queries) | |||
| copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Linear Mode
