Vlookup Question

[Siops]

Good Day,

Is it possible to vlookup a value and will show either the second match or third match? I'm trying to vlookup a value but it will only show the first match.

 

[T. Valko]

Sir, do you have any IM?

Sorry, I have that turned off.

I was getting too many private message requests for help so I had to turn that off.

 

[Siops]

I have a follow up question, Sir. I tried to use this on my other files but instead of getting blanks, I'm getting #NUM!. I'm not sure what I did wrong but it worked on my other file.

=IF(COLUMNS($M7:M7)="YES","",SMALL(IF(Raw!$B$2:$B$1391=Incentive!B7,
IF(Raw!$U$2:$U$1391=1,Raw!$W$2:$W$1391)),COLUMNS(Incentive!$M7:M7)))

 

[T. Valko]

I have a follow up question, Sir. I tried to use this on my other files but instead of getting blanks, I'm getting #NUM!. I'm not sure what I did wrong but it worked on my other file.

=IF(COLUMNS($M7:M7)="YES","",SMALL(IF(Raw!$B$2:$B$1391=Incentive!B7,
IF(Raw!$U$2:$U$1391=1,Raw!$W$2:$W$1391)),COLUMNS(Incentive!$M7:M7)))

The problem would be with that highlighted portion.

The COLUMNS(...) function returns a number so:

=IF(COLUMNS($M7:M7)="YES"...

Will always be FALSE.

We use the COLUMNS function to count how many cells the formula is being entered in compared to how many times the criteria is present.

Consider this data:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Max</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">9/1/2011</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">YES</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Greg</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">9/1/2011</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">YES</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Max</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">9/2/2011</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">NO</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Greg</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">9/2/2011</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">YES</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Max</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">9/3/2011</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">NO</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Tom</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">9/4/2011</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">YES</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Sue</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">9/4/2011</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">NO</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Max</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">9/6/2011</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">YES</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Greg</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">9/7/2011</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">NO</TD></TR></TBODY></TABLE>


If we are looking for the dates that Max was absent then we need to know just how many times Max was absent.

In this sample data Max was absent 2 times. So, that means we need at least 2 formula cells to return the dates that Max was absent.

We check the number of cells the formula is being entered in by using the COLUMNS function. If the number of cells the formula is being entered into is greater than the number of times Max was absent then we want those cells to appear blank.

As you drag copy the formula across a row the COLUMNS function will return an incrementing count. We compare that count to the number of times Max was absent. When the count is greater than the number of absences then we tell the formula to return a blank.

So, in your formula you want the COLUMNS function to be compared to a number and not the text "Yes".

See how I did it in the sample file I posted. Column F contains a formula that gets the count of absences for each person. Then we compare that number to the result of the COLUMNS function.

 

[Siops]

Woosh! It's now working again. Sorry for another follow up question. What if instead of Date, i want to show a text, not a number or a date. Is it possible?

 

[T. Valko]

Woosh! It's now working again. Sorry for another follow up question. What if instead of Date, i want to show a text, not a number or a date. Is it possible?

Yes, it's possible.

Need more details to make a specific suggestion.

 

[Siops]

I actually followed the same formula but replaced the one on red font with different cells. Instead of Raw!$W$2:$W$1391, I used Raw!$V$2:$V$1391. That cells contains a text data.


IF(COLUMNS($M7:M7)="YES","",SMALL(IF(Raw!$B$2:$B$1391=Incentive!B7,
IF(Raw!$U$2:$U$1391=1,Raw!$W$2:$W$1391)),COLUMNS(Incentive!$M7:M7)))

 

[T. Valko]

I actually followed the same formula but replaced the one on red font with different cells. Instead of Raw!$W$2:$W$1391,I used Raw!$V$2:$V$1391. That cells contains a text data.


IF(COLUMNS($M7:M7)="YES","",SMALL(IF(Raw!$B$2:$B$1391=Incentive!B7,
IF(Raw!$U$2:$U$1391=1,Raw!$W$2:$W$1391)),COLUMNS(Incentive!$M7:M7)))

OK, that highlighted portion is incorrect. See my explanation about that in post #13.

 

[Siops]

Sorry, forgot to edit that part. That part is actually working already. My new problem is when I tried to copy the same formula but change the Raw!$W$2:$W$1391 intoRaw!$V$2:$V$1391 which is a text, I'm getting #NUM!.


=IF(COLUMNS($N4:N4)>$M4,"",SMALL(IF(Raw!$B$2:$B$1391=Incentive!B4,
IF(Raw!$U$2:$U$1391=1,Raw!$W$2:$W$1391)),COLUMNS(Incentive!$N4:N4)))

 

[T. Valko]

Sorry, forgot to edit that part. That part is actually working already. My new problem is when I tried to copy the same formula but change the Raw!$W$2:$W$1391 intoRaw!$V$2:$V$1391 which is a text, I'm getting #NUM!.


=IF(COLUMNS($N4:N4)>$M4,"",SMALL(IF(Raw!$B$2:$B$1391=Incentive!B4,
IF(Raw!$U$2:$U$1391=1,Raw!$W$2:$W$1391)),COLUMNS(Incentive!$N4:N4)))

Use WORDS to explain what you're trying to do. Posting sample data is ALWAYS a big help.

 

[Siops]

On Column L, I used the formula that you gave me earlier. Now on Column M2, this should show as "LATE" which is came from Column H.