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.

 

[WinteE]

=VLOOKUP() will always get you the first instance.

 

[T. Valko]

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.

See this...

Lookup when there are multiple instances of the lookup value

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

 

[Siops]

Thank you sirs for your quick response. This is what my data looks like. For ColumnG, I want to show the date of absent for Max.



<table width="446" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="3"> <col style="width: 33pt;" width="44"> <col style="width: 83pt;" width="110"> <col style="width: 75pt;" width="100"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">A</td> <td class="xl65" style="width: 48pt;" width="64">B</td> <td class="xl65" style="width: 48pt;" width="64">C</td> <td class="xl65" style="width: 33pt;" width="44">E</td> <td class="xl65" style="width: 83pt;" width="110">F</td> <td class="xl65" style="width: 75pt;" width="100">G</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">NAME</td> <td class="xl65">DATE</td> <td class="xl65">PRESENT</td> <td class="xl65">Name</td> <td class="xl65"># of Days Absent</td> <td class="xl65">Date of Absent</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">Max</td> <td class="xl66">1-Sep</td> <td class="xl65">YES</td> <td class="xl65">Max</td> <td class="xl65">1</td> <td class="xl65">xx</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">Greg</td> <td class="xl66">1-Sep</td> <td class="xl65">YES</td> <td class="xl65">Greg</td> <td class="xl65">0</td> <td class="xl65">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">Max</td> <td class="xl66">2-Sep</td> <td class="xl65">NO</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">Greg</td> <td class="xl66">2-Sep</td> <td class="xl65">YES</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> </tbody></table>

 

[T. Valko]

Thank you sirs for your quick response. This is what my data looks like. For ColumnG, I want to show the date of absent for Max.



<TABLE cellSpacing=0 cellPadding=0 width=446 border=0><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 33pt" width=44><COL style="WIDTH: 83pt" width=110><COL style="WIDTH: 75pt" width=100><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="WIDTH: 48pt; HEIGHT: 15pt" width=64 height=20>A</TD><TD class=xl65 style="WIDTH: 48pt" width=64>B</TD><TD class=xl65 style="WIDTH: 48pt" width=64>C</TD><TD class=xl65 style="WIDTH: 33pt" width=44>E</TD><TD class=xl65 style="WIDTH: 83pt" width=110>F</TD><TD class=xl65 style="WIDTH: 75pt" width=100>G</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" height=20>NAME</TD><TD class=xl65>DATE</TD><TD class=xl65>PRESENT</TD><TD class=xl65>Name</TD><TD class=xl65># of Days Absent</TD><TD class=xl65>Date of Absent</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" height=20>Max</TD><TD class=xl66>1-Sep</TD><TD class=xl65>YES</TD><TD class=xl65>Max</TD><TD class=xl65>1</TD><TD class=xl65>xx</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" height=20>Greg</TD><TD class=xl66>1-Sep</TD><TD class=xl65>YES</TD><TD class=xl65>Greg</TD><TD class=xl65>0</TD><TD class=xl65>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" height=20>Max</TD><TD class=xl66>2-Sep</TD><TD class=xl65>NO</TD><TD class=xl65>

</TD><TD class=xl65>

</TD><TD class=xl65>

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" height=20>Greg</TD><TD class=xl66>2-Sep</TD><TD class=xl65>YES</TD><TD class=xl65>

</TD><TD class=xl65>

</TD><TD class=xl65>

</TD></TR></TBODY></TABLE>

Try this...

Enter this array formula** in G2:

=IF(COLUMNS($G2:G2)>$F2,"",SMALL(IF($A$2:$A$5=$E2,IF($C$2:$C$5="no",$B$2:$B$5)),COLUMNS($G2:G2)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date

Copy down to G3 then across until you get a full column of blanks.

 

[Siops]

Thank you again for your reply, Sir. After doing formula you mentioned, the result was #NUM!

 

[Siops]

Hi Sir,

I tried it again and the formula is working. Thank you very much!

 

[T. Valko]

Thank you again for your reply, Sir. After doing formula you mentioned, the result was #NUM!

Here's a small sample file that demonstrates this.

zzzSiops.xls 17kb

http://cjoint.com/?AIoq2obk3I8

 

[T. Valko]

Hi Sir,

I tried it again and the formula is working. Thank you very much!

Good deal. Thanks for the feedback!

 

[Siops]

Sir, do you have any IM?