+ Reply to Thread
Results 1 to 8 of 8

Assistance Deciphering Vlookup

  1. #1
    Registered User
    Join Date
    04-23-2022
    Location
    Texas
    MS-Off Ver
    2018
    Posts
    3

    Question Assistance Deciphering Vlookup

    Hi,
    I took over a workbook that I am trying to decipher. I am specifically confused with the cell references such as $W$1 i the second VLOOKUP. I am used to seeing the number of columns but what does W1 reference? The sheet it is referencing doesn't have a column with data in W1. This whole formula is pretty complex but any help with breaking it down would be awesome.
    Thank you!




    =IF(AK5="Completed",VLOOKUP(AI$3&$A5,'SF Stage Current'!$A:$Z,8,0),IF(AK5="N/A","N/A",IF(VLOOKUP(AI$3&$A5,'SF Stage Current'!$A:$Z,$W$1,0)="",VLOOKUP(AI$3&$A5,'SF Stage Current'!$A:$Z,$S$1,0),VLOOKUP(AI$3&$A5,'SF Stage Current'!$A:$Z,$W$1,0))))

  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: Assistance Deciphering Vlookup

    The third parameter of the VLOOKUP function determines which column of the lookup table (which is the second parameter) any data should be returned from.

    The first VLOOKUP in your formula has 8 as the third parameter, so it should return data from the 8th column, which will be from column H.

    The other VLOOKUP functions have $W$1 and $S$1 as the 3rd parameter, so those two cells should contain a number which relates to the column from which to get the data.

    Hope this helps.

    Pete

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Assistance Deciphering Vlookup

    The sheet it is referencing doesn't have a column with data in W1.
    W1 is not on the sheet being referenced, it is on the sheet where the VLOOKUP function is. So, the same sheet as AI$3&$A5.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Assistance Deciphering Vlookup

    In terms of what it does:

    =IF(AK5="Completed", if cell AK5 on the sheet containing the formula contains Completed

    VLOOKUP(AI$3&$A5,'SF Stage Current'!$A:$Z,8,0), do this VLOOKUP

    IF(AK5="N/A" if cell AK5 on the sheet containing the formula contains N/A

    "N/A", return N/A

    IF(VLOOKUP(AI$3&$A5,'SF Stage Current'!$A:$Z,$W$1,0)="", if the result of this VLOOKUP is a null (blank) return

    VLOOKUP(AI$3&$A5,'SF Stage Current'!$A:$Z,$S$1,0), do this VLOOKUP

    VLOOKUP(AI$3&$A5,'SF Stage Current'!$A:$Z,$W$1,0)))) otherwise do this VLOOKUP
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    04-23-2022
    Location
    Texas
    MS-Off Ver
    2018
    Posts
    3

    Re: Assistance Deciphering Vlookup

    Thank everyone for their input on this. This clears it up for me and exactly what I was needing help with. After understanding this there is a value in W1 which is 8, so the column is referencing the value in column 8. Makes sense now thank you all!

  6. #6
    Registered User
    Join Date
    04-23-2022
    Location
    Texas
    MS-Off Ver
    2018
    Posts
    3

    Question Re: Assistance Deciphering Vlookup

    Hi Ali ,

    First off thank you for your explanation. I Just want to clarify this is a different way to make sure I am understanding correctly.

    The reason I ask because two vlookups in the last three statements reference the same value AI$3&$A5,'SF Stage Current'!$A:$Z,$W$1,0 so is it saying that if blank then lookup one value otherwise re enter the value that should be blank?
    Last edited by AliGW; 04-25-2022 at 01:45 AM. Reason: PLEASE don't quote unnecessarily!

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Assistance Deciphering Vlookup

    Like Ali said, if the value returned from the VLOOKUP using $W$1 is blank, then use the Value returned from the VLOOKUP using $S$1, otherwise use the value from the VLOOKUP using $W$1 (non blank value).

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Assistance Deciphering Vlookup

    is it saying that if blank then lookup one value otherwise re enter the value that should be blank?
    What it is saying is this:

    If the VLOOKUP using the W1 column reference returns blank, use the VLOOKUP with the S1 column reference instead.

    An IF statement has three bits to it:

    =IF(VLOOKUP W1 returns blank, do VLOOKUP with S1, otherwise do VLOOKUP with W1)

    If you don't have the final section, then anything that does not meet the criterion will return FALSE.

+ 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] Deciphering a formula?
    By AmyV1 in forum Excel Formulas & Functions
    Replies: 33
    Last Post: 01-08-2018, 12:10 AM
  2. Deciphering VBA
    By jp45 in forum Excel General
    Replies: 2
    Last Post: 02-01-2017, 12:24 PM
  3. Function deciphering...
    By EXCEL40294 in forum Excel General
    Replies: 3
    Last Post: 06-09-2015, 03:14 PM
  4. [SOLVED] Help deciphering formula
    By bdouglas1011 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-21-2014, 05:01 PM
  5. [SOLVED] Help Deciphering formula?
    By bbecht01 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-14-2013, 10:30 PM
  6. Deciphering a Formula
    By Winstonwolf in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-29-2012, 04:05 PM
  7. Another one i need deciphering...
    By Dan Mackman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-26-2007, 06:04 PM

Tags for this Thread

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