+ Reply to Thread
Results 1 to 7 of 7

Lookup part number in column and return heading if found

  1. #1
    Registered User
    Join Date
    04-04-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    28

    Lookup part number in column and return heading if found

    Hi, I have an excel 2010 spreadsheet with column A having 4000 part numbers (Alpha Numeric) the column is broken into "Groups" with each group having a heading.
    I am trying to search the column for a part number and return its group heading. I cannot toy with the column as it is maintained by another department
    Can anyone assist.

    Thanks in anticipation
    Andre Van Der Walt

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Lookup part number in column and return heading if found

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Quang PT

  3. #3
    Registered User
    Join Date
    04-04-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Lookup part number in column and return heading if found

    I would like to search for "BCB022P06OF" and if found I would like it to return "0.22mm˛ TCW Multi-Pair"
    I hope this makes sense.example.xlsx

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Lookup part number in column and return heading if found

    Assuming D2 contains "BCB022P06OF", last row is 7263

    I will try to locate the upper "code" title (cell A5), then get the group title above it (cell A4):

    =LOOKUP(MATCH($D$2,$A$1:$A$7263,0),IF($A$2:$A$7263="Code",ROW($A$2:$A$7263),""),$A$1:$A$7262)

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Last edited by bebo021999; 10-14-2015 at 01:26 PM.

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Lookup part number in column and return heading if found

    Try

    =LOOKUP(2,1/(B$2:INDEX(B:B,MATCH("BCB022P06OF",A:A,0))="Description"),A:A)

  6. #6
    Registered User
    Join Date
    04-04-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Lookup part number in column and return heading if found

    Thank you bebo021999, your solution does exactly what I required.
    Bryan, TX thanks for your solution, only problem is that the part number being searched for has to be entered into the formula, whereas bebo021999's solution refers to the contents of a cell

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Lookup part number in column and return heading if found

    Quote Originally Posted by Gorbs View Post
    Bryan, TX thanks for your solution, only problem is that the part number being searched for has to be entered into the formula, whereas bebo021999's solution refers to the contents of a cell
    You can do that with this formula as well.
    I hard coded it because you never specified which cell would contain the code (or even that you wanted it to).

    =LOOKUP(2,1/(B$2:INDEX(B:B,MATCH(D2,A:A,0))="Description"),A:A)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Lookup MIN in 3 separate ranges, return col. heading
    By PeteABC123 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-15-2014, 03:46 PM
  2. [SOLVED] Whick lookup needed to return a column heading?
    By Thistledown in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-11-2014, 08:08 AM
  3. [SOLVED] Return Column Number when specific text is found
    By Simon.xlsx in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-29-2014, 12:20 PM
  4. lookup values in a range,return corresponding column heading
    By aljaffa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2011, 04:31 PM
  5. Return number of days until a date found in a lookup table
    By midwestgirl80 in forum Excel General
    Replies: 1
    Last Post: 08-08-2009, 10:07 PM
  6. Formula to return column heading dependent on number in cell
    By Reidm in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-31-2008, 05:04 AM
  7. Increase Column Heading Part 2
    By Adam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-07-2006, 11:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1