+ Reply to Thread
Results 1 to 8 of 8

Search/lookup to return column and row names...

  1. #1
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Search/lookup to return column and row names...

    I have a large table, it has rows and columns...er


    My tables have a lot of numbers, and I want to include a 'search' to look for them. I can't seem to figure out the right combination of vlookup/hlookup/index/match/all that jazz so I come asking for help..I have a feeling this is a really simple solution...programmers block? :\

    I have included a sample workbook, this is not my data but it shows the desired result of what I am trying to do

    searchexample.xlsx

    Thanks!
    Last edited by Speshul; 04-20-2012 at 01:00 PM.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Search/lookup to return column and row names...

    Put this array formula in cell F14:

    =INDEX($B$1:$K$1, LOOKUP(SUM((($B$2:$K$11=E14) * COLUMN($B$2:$K$11))), {1,2,3,4,5,6,7,8,9,10})-1) & ", " & INDEX($A$2:$A$11, LOOKUP(SUM((($B$2:$K$11=E14) * ROW($B$2:$K$11))), {1,2,3,4,5,6,7,8,9,10})-1)

    ...and confirm it by pressing CTRL-SHIFT-ENTER to activate the array. Section 5, Area 8 should appear.

    Now copy that cell to F18. You should get an #N/A result since the number in E18 is not available in the table. Change that value to 4565 and a new result will appear.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Search/lookup to return column and row names...

    Perfect! Thank you

    I guess I need to learn how to use arrays next

  4. #4
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,283

    Re: Search/lookup to return column and row names...

    Try this

    =INDEX(B1:K1,MIN(IF(B2:K11=E14,COLUMN(B1:K1)-MIN(COLUMN(B1:K1))+1,"")))&","&
    INDEX(A2:A11,MIN(IF(B2:K11=E14,ROW(A2:A11)-MIN(ROW(A2:A11))+1,"")))

  5. #5
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,283

    Re: Search/lookup to return column and row names...

    This is the one I really wanted to post but couldn't get it working first time

    =INDEX(B1:K1,0,MATCH(TRUE,MMULT(TRANSPOSE(--(B2:K11=E14)),(ROW(B2:K11)^0))>0))&","&
    INDEX(A2:A11,MATCH(TRUE,MMULT(--(B2:K11=E14),TRANSPOSE(COLUMN(B2:K11)^0))>0),0)

  6. #6
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Search/lookup to return column and row names...

    After tweaking and modifying, the second example works for my sheet, the first didn't return the right row - probably my fault as I don't understand the formula enough to be able to adjust it with any confidence.

    The table, excluding the row and column names, is 57 columns wide and 28 rows tall.


    This formula worked perfect
    Please Login or Register  to view this content.
    I'm sure this one would have, I'm guessing I don't understand it enough to modify it properly for my table though
    Please Login or Register  to view this content.

  7. #7
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Search/lookup to return column and row names...

    Except it gives a result if the search field is blank...i think I can fix that though

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Search/lookup to return column and row names...

    can probably shorten that long array with an INDIRECT(ROW(1:57)) trick.

    So my original formula would change to:

    =INDEX(Routing!$B$9:$BF$9, LOOKUP(SUM(((Routing!$B$10:$BF$37=F3) * COLUMN(Routing!$B$10:$BF$37))), INDIRECT(ROW(1:57)))-1) & ", " & INDEX(Routing!$A$10:$A$37, LOOKUP(SUM(((Routing!$B$10:$BF$37=F3) * ROW(Routing!$B$10:$BF$37))), INDIRECT(ROW(1:28)))-1)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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