+ Reply to Thread
Results 1 to 2 of 2

VLOOKUP or INDEX/MATCH with multiple column index numbers

  1. 02-22-2017, 05:57 PM #1
    cerebral87
    cerebral87 is offline
    Registered User
    Join Date
    02-22-2017
    Location
    New York
    MS-Off Ver
    Excel 2013
    Posts
    1

    VLOOKUP or INDEX/MATCH with multiple column index numbers

    I am trying to look up the value to the right of a name. Normally I would use VLOOKUP but there are twelve different columns where the name could be. This also means there are twelve possible columns for the return value.

    What I have tried so far is the below. This was just to see if it would work for two columns. If it did I was planning to nest it 12 times. "draft" is my name for the entire table it would be searching in.

    =IF(ISERROR(VLOOKUP(A3,draft,Draft!$C$1,FALSE)),VLOOKUP(A3, draft,Draft!$F$1),VLOOKUP(A3,draft,Draft!$C$1,FALSE))

    Of course that didn't work so would appreciate any advice.
    Reply With QuoteRegister To Reply
  2. 02-22-2017, 07:13 PM #2
    loginjmor
    loginjmor is offline
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: VLOOKUP or INDEX/MATCH with multiple column index numbers

    Hi -

    It's hard to really tell what you're doing from a verbal description. Attached is a spreadsheet that does what I think you're describing. It has an array of names and values spread across Columns A:F and Rows 1:4. Cell I1 has the name I'm searching for. Cell I2 contains this formula:

    =INDEX(A1:F4,SUMPRODUCT((A1:F4=I1)*ROW(A1:A4)),SUMPRODUCT((A1:F4=I1)*COLUMN(A:F))+1)

    as you change the name in cell i1, INDEX returns the value immediately to the right of the name. To go to 12 columns, just increase the ranges.

    Hope this helps.
    Attached Files Attached Files
    ____________________________________________
    If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write?
    By Renejorgensen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2016, 10:54 AM
  2. Find value from multiple rows and put in in column vlookup or index match
    By Raehan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-28-2016, 02:56 PM
  3. converting vlookup to index match across multiple sheets - fails if no match on 1st sheet
    By nisiwi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-18-2015, 08:45 AM
  4. Index, match, dynamic and connected without sorting helper column non-adjacent index match
    By Underling in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  5. [SOLVED] Help! Vlookup, Index Match match, sumproduct? Matching text with multiple conditions
    By chicolocal in forum Excel General
    Replies: 13
    Last Post: 12-13-2012, 11:44 AM
  6. [SOLVED] Formula (VLOOKUP vs INDEX & MATCH) to return multiple values in the same column
    By wfidler in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-06-2012, 07:04 PM
  7. Vlookup or index/match with multiple column and criteria?
    By xirx in forum Excel General
    Replies: 2
    Last Post: 07-22-2005, 08:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1