+ Reply to Thread
Results 1 to 4 of 4

Use the Indirect formula with Match and Index to find data in unsorted column

  1. #1
    Registered User
    Join Date
    12-02-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 2003
    Posts
    21

    Use the Indirect formula with Match and Index to find data in unsorted column

    I am trying to figure out a way to lookup data in column B and pull the resulting data from column A. The data is not sorted. The Lookup and Vlookup commands did not work and someone suggested the index and match formula. That works when there are no variables. I want to use this formula with the indirect command. What if there is a field that identifies the month (B8), and depending on the month in that field, the formula will pull the data from the right worksheet – in this case January. I want to use the variable in column B8. Meaning, I can change the January to February and it will find the data on the February worksheet instead? I’m sure the indirect command will work, but I can’t figure out the syntax.

    The Division # in A9 can be alphanumeric so a numbers based formula won’t work.

    I tried =+INDEX(+INDIRECT("'"&TEXT(B8,"mmmm")&" JV'!A2:A6"),MATCH(C105,+INDIRECT("'"&TEXT(B8,"mmmm")&" JV'!b2:b6"),0)), but it doesn't work. I'm getting a "#Value!" error

    Can someone help?


    Column A Column B
    1 Units Division
    2 450 9658
    3 250 1112A
    4 525 5634
    5 125 3251
    6 350 2796CDR


    7
    8 RESULTS FOR MONTH OF JANUARY
    9 5634 =INDEX($A$2:$A$6,MATCH(A9,$B$2:$B$6,0)) Result should be 525

  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: Use the Indirect formula with Match and Index to find data in unsorted column

    Based on your example at the end, the formula in B9 would be:

    =INDEX(A1:A6, MATCH(A9, B1:B9, 0))

    You should probably upload a sample workbook manually demonstrating what you're trying to do if that formula doesn't work. I don't see the application of INDIRECT() in this text example, a workbook should allow you to demonstrate more clearly.

    Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.
    _________________
    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
    Registered User
    Join Date
    12-02-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Use the Indirect formula with Match and Index to find data in unsorted column

    Obiously I can't place the file out here, but I did create one in the basic form of what I want. As you can see, what I'd like the formula to do is to change which worksheet it picks up the data from. If the "Month" field changes from January to February, it should automatically pick up the data from the correct worksheet. I think the indirect.ext command will act as a variable, but I am having trouble with the syntax.
    Attached Files Attached Files

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

    Re: Use the Indirect formula with Match and Index to find data in unsorted column

    In C14, then copied down:

    =INDEX(INDIRECT("'" & $B$2 & "'!$A:$A"),MATCH(B14,INDIRECT("'" & $B$2 & "'!$B:$B"),0))

+ 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