# Assistance Deciphering Vlookup

1. ## 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))))  Register To Reply

2. ## 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  Register To Reply

3. ## 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.  Register To Reply

4. ## 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  Register To Reply

5. ## 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!  Register To Reply

6. ## 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?  Register To Reply

7. ## 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).  Register To Reply

8. ## 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.  Register To Reply

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