+ Reply to Thread
Results 1 to 8 of 8

Nested VLOOKUP - help please!

  1. #1
    Forum Contributor
    Join Date
    06-16-2010
    Location
    UK
    MS-Off Ver
    Excel 2010/13
    Posts
    140

    Nested VLOOKUP - help please!

    Hi

    I'd appreciate some help on how to nest a VLOOKUP (or a better method to do the following)

    In Cell BE3 I have a persons name EG 'F Smith'
    in Cell BF3 I have the reference T1I1

    In cell BG3 I want to look up from a table in cells A3:Q12 what matches the combination of BE3 and BF3

    In the table, the peoples names are in column 4 and the result to be returned to cell BG3 will come from column 16

    Hope all that makes sense.
    Can't seem to get this right.

    Many thanks

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

    Re: Nested VLOOKUP - help please!

    Post an example workbook, then it should be easier to see how your data is laid out.

    Pete

  3. #3
    Forum Contributor
    Join Date
    06-16-2010
    Location
    UK
    MS-Off Ver
    Excel 2010/13
    Posts
    140

    Re: Nested VLOOKUP - help please!

    OK, sample data is attached.

    I've removed data from the T2I1/T2I2/T3I1/T3I2/T4I1/T4I2/T5I1/T5I2 areas, but essentially they are the same size as areas for T1I1 and T2I2
    I have added Comments to Cells BG3 and BH3 to show what data I need to extract from the table.

    Hopefully this is clear.
    The data in the table (Columns A to U) actually goes to a pivot table, but there are 2 columns I can't create in the pivot, so this data will be looked up from the 'helper' table in columns BE:BH in the form (in the example of Mr Smith) 1-40 (IE "Wick"-"Run")
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-26-2013
    Location
    Bradford
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Nested VLOOKUP - help please!

    A pivot will give you the answer you require without using lookups - and can be reported on in any format you like at whatever level of detail you need. see attached.
    Attached Files Attached Files

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

    Re: Nested VLOOKUP - help please!

    Try this in BG3
    =VLOOKUP($BE$3,OFFSET($D$3:$U$13,MATCH($BF3,$A$3:$A$40,0)-1,0),13,0)

    Change the 13 to 14 for BH3

  6. #6
    Forum Contributor
    Join Date
    06-16-2010
    Location
    UK
    MS-Off Ver
    Excel 2010/13
    Posts
    140

    Re: Nested VLOOKUP - help please!

    Quote Originally Posted by MarieR View Post
    A pivot will give you the answer you require without using lookups - and can be reported on in any format you like at whatever level of detail you need. see attached.
    Thanks Marie, I had thought of a pivot - I use them extensively, but I need to do some ranking on the results and just add the best result for each person as a lookup alongside my main pivot table that is derived from the main data table in my example.
    IE - I do a division of wick/run for each result and rank them to arrive at the best figures to feed to the main pivot.

    I had originally referenced all those cells in columns BG and BH just using ='cell' but that's messy and prone to error.

  7. #7
    Forum Contributor
    Join Date
    06-16-2010
    Location
    UK
    MS-Off Ver
    Excel 2010/13
    Posts
    140

    Re: Nested VLOOKUP - help please!

    Quote Originally Posted by Jonmo1 View Post
    Try this in BG3
    =VLOOKUP($BE$3,OFFSET($D$3:$U$13,MATCH($BF3,$A$3:$A$40,0)-1,0),13,0)

    Change the 13 to 14 for BH3
    Thank you Jonmo1 - that's perfect.

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

    Re: Nested VLOOKUP - help please!

    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] Nested IF or Vlookup?
    By zhills63 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-24-2013, 11:04 AM
  2. Vlookup across sheets, nested Vlookup possibly?
    By paid2mkgrlspanic in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 06-09-2009, 05:10 PM
  3. Nested IF with a VLOOKUP?
    By plunk25 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-25-2006, 01:10 PM
  4. [SOLVED] Can Someone Help me With a Nested VLOOKUP
    By lee.dykeman@monro.com in forum Excel General
    Replies: 1
    Last Post: 12-17-2005, 10:30 AM
  5. Nested If with VLOOKUP's... I think!
    By Tim C in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 06: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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1