Conditional Vlookup

One way of doing this would be to concatenate the first two columns and then use the result of that for the lookup.

eg

<TABLE BORDER CELLSPACING=1 CELLPADDING=9 WIDTH=394>
<TR><TD WIDTH="8%" VALIGN="TOP" BGCOLOR="#ffffff">TD>
<TD WIDTH="23%" VALIGN="TOP" BGCOLOR="#ffffff">
<FONT FACE="Arial" SIZE=3>A</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP" BGCOLOR="#ffffff">
<FONT FACE="Arial" SIZE=3>B</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP" BGCOLOR="#ffffff">
<FONT FACE="Arial" SIZE=3>C</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP" BGCOLOR="#ffffff">
<FONT FACE="Arial" SIZE=3>D</FONT></TD>
</TR>
<TR><TD WIDTH="8%" VALIGN="TOP" BGCOLOR="#ffffff">
<FONT FACE="Arial" SIZE=3>1</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>a4</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>a</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>4</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>mick</FONT></TD>
</TR>
<TR><TD WIDTH="8%" VALIGN="TOP" BGCOLOR="#ffffff">
<FONT FACE="Arial" SIZE=3>2</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>g7</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>g</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>7</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>john</FONT></TD>
</TR>
<TR><TD WIDTH="8%" VALIGN="TOP" BGCOLOR="#ffffff">
<FONT FACE="Arial" SIZE=3>3</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>k6</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>k</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>6</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>fred</FONT></TD>
</TR>
<TR><TD WIDTH="8%" VALIGN="TOP" BGCOLOR="#ffffff">
<FONT FACE="Arial" SIZE=3>4</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>a9</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>a</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>9</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>peter</FONT></TD>
</TR>
<TR><TD WIDTH="8%" VALIGN="TOP" BGCOLOR="#ffffff">
<FONT FACE="Arial" SIZE=3>5</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>h0</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>h</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>0</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>tom</FONT></TD>
</TR>
<TR><TD WIDTH="8%" VALIGN="TOP" BGCOLOR="#ffffff">
<FONT FACE="Arial" SIZE=3>6</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>k2</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>k</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>2</FONT></TD>
<TD WIDTH="23%" VALIGN="TOP">
<FONT FACE="Arial" SIZE=3>paul</FONT></TD>
</TR>
</TABLE>

Formulas...

D2, copied down:

=A2&CHAR(127)&B2

H2:

=INDEX($C$2:$C$7,MATCH(F2&CHAR(127)&G2,$D$2:$D$7,0))

Thanks!

Been using the formula as it works great except for one thing, if the value does not appear in the range then it Returns #N/A which is really annoying because I use the reults of the formula on a calculation which wont work if #N/A is returned, anybody know a way round this?

uk said:

Been using the formula as it works great except for one thing, if the value does not appear in the range then it Returns #N/A which is really annoying because I use the reults of the formula on a calculation which wont work if #N/A is returned, anybody know a way round this?

Click to expand...

Fearing that you don't want a 2-cell approach (using an additional cell)...

=IF(ISNUMBER(MATCH(F2&CHAR(127)&G2,$D$2:$D$7,0)),INDEX($C$2:$C$7,MATCH(F2&CHAR(127)&G2,$D$2:$D$7,0)),"")

A 2-cell approach:

I2:

=MATCH(F2&CHAR(127)&G2,$D$2:$D$7,0)

H2:

=IF(ISNUMBER(I2),INDEX($C$2:$C$7,I2),"")

which is the result cell.

Works a charm, thanks!