+ Reply to Thread
Results 1 to 6 of 6

2 Way Lookup with a dynamic row

  1. #1
    Registered User
    Join Date
    05-12-2010
    Location
    Texas, US
    MS-Off Ver
    Excel 2003
    Posts
    7

    2 Way Lookup with a dynamic row

    Hi,

    I have the following data on another tab that is refreshed.

    Test.xlsx

    I need to be able to change the Code/Date on cells B14/B15 and have the close numbers come up.
    I thought that I would be able to do it with a index/match 2 way lookup but the problem is when the data is missing for dates which causes the row to break.

    Does anyone know of a code that would work?

    I tried the following formula: =INDEX(A1:K7,MATCH(B15,B3:B7,0)+2,MATCH(B14,A1:K1,0)+1) however when you try using it on IA0001 it wont work due to missing data. Is there any work around?
    Last edited by greenmonke; 10-21-2014 at 05:04 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: 2 Way Lookup with a dynamic row

    Hi greenmonke,

    How about using some Named Ranges for your A, B and C blocks. Then use a VLookup like the attached.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    05-12-2010
    Location
    Texas, US
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: 2 Way Lookup with a dynamic row

    Hi MarvinP,

    I thought about that, but this is a small snapshot of what I have. There are quite a few of these blocks so I am hoping for something that wont require alot of naming.

    Thanks!

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

    Re: 2 Way Lookup with a dynamic row

    Try

    =VLOOKUP(B15,OFFSET(B3:C7,0,MATCH(B14,B1:K1,0)-1),2,0)

  5. #5
    Registered User
    Join Date
    05-12-2010
    Location
    Texas, US
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: 2 Way Lookup with a dynamic row

    Hi Jonmo1,

    Perfect! thank you so much for your help!

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

    Re: 2 Way Lookup with a dynamic row

    You're welcome.

+ 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] Dynamic type lookup
    By Heirak in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-28-2013, 11:13 AM
  2. [SOLVED] Dynamic Lookup
    By nathanhamilton82 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-18-2012, 04:45 PM
  3. Lookup Against Dynamic Range Using VBA
    By dgo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-01-2009, 08:44 AM
  4. [SOLVED] dynamic lookup
    By Giantrobot in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-16-2006, 07:15 PM
  5. [SOLVED] using LOOKUP instead of IF on dynamic row
    By Jay C in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-08-2005, 08:06 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