+ Reply to Thread
Results 1 to 6 of 6

VLOOKUP with a varying range?

  1. #1
    Registered User
    Join Date
    04-26-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2007
    Posts
    18

    VLOOKUP with a varying range?

    Hello

    I'm trying to put order to some raw data but I've hit a wall with VLOOKUP. I've attached some example data with a desired outcome but some help with the formula to achieve this would be greatly appreciated.

    Basically, in N3:Q10 I'm trying to find a formula to lookup the corresponding reference in column M within the raw data (A2:K10) taking into consideration the corresponding date in row 2. For example, Q9 would return the value in K6 (PRW01 on 03/08/20).

    I think I need the nested INDEX & MATCH functions but I just can't get it to work.


    Thanks
    Demesne
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: VLOOKUP with a varying range?

    You can use this formula in N3:

    =IFERROR(INDEX($A$3:$K$10,MATCH($M3,INDEX($A$3:$K$10,0,MATCH(N$2,$A$2:$K$2,0)),0),MATCH(N$2,$A$2:$K$2,0)+1),"")

    then copy across and down as required.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    04-26-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: VLOOKUP with a varying range?

    Pete

    Thanks Very much. Works perfectly and is a big help. Now onto the next problem... :D

  4. #4
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: VLOOKUP with a varying range?

    N3 cell , array formula , Drag down and accross

    HTML Code: 
    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: VLOOKUP with a varying range?

    You're welcome, Demesne, and thanks for the rep.

    Pete

  6. #6
    Registered User
    Join Date
    04-26-2013
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: VLOOKUP with a varying range?

    Thanks wk9128. This solution also works and has given me another avenue to explore.

+ 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] Add IF Statement formula to cells in varying range and paste to used range
    By Webbers in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-26-2015, 01:28 PM
  2. Multiple IF, vlookup conditions and varying results
    By dh1231 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-30-2014, 12:14 PM
  3. [SOLVED] Varying validation drop-downs based on varying cell values
    By navarreman1 in forum Excel General
    Replies: 2
    Last Post: 08-08-2013, 08:13 AM
  4. VLOOKUP - Varying Positions and Values
    By croeder in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-26-2013, 06:16 PM
  5. [SOLVED] Dynamic Range Reference, Varying Size of Range
    By cdiaz in forum Excel General
    Replies: 2
    Last Post: 06-30-2011, 02:49 PM
  6. VLOOKUP - varying the LOOKUP
    By adscrim in forum Excel General
    Replies: 3
    Last Post: 03-20-2007, 09:23 AM
  7. Using a Macro to perform a VLookup on varying number of rows
    By Shirley Munro in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-05-2005, 09:28 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