Simple VLookup help needed

[23hawk]

I need to do a look up on sheet1 -looks like this : =VLOOKUP(A4,Sheet2!A:B,2,FALSE) works fine BUT I need it to do more than that:
Now if there is no match then it needs to do a look up on sheet 3 to find a value. How do I write a formula for that?

 

[VoG]

Try

=IF(ISNA(VLOOKUP(A4,Sheet2!A:B,2,FALSE)),VLOOKUP(A4,Sheet3!A:B,2,FALSE),VLOOKUP(A4,Sheet2!A:B,2,FALSE))

 

[jeffreybrown]

This could be one way...

=IFERROR(VLOOKUP(A4,Sheet2!A:B,2,FALSE),VLOOKUP(A4,Sheet3!A:B,2,FALSE))

Edit: Yes maybe Peter's is better as the formula above will only work 2007+

 

[23hawk]

Thank-you all very much- works perfectly - I got all tangled up in the "(())"

 

[theozz]

[ Search Value In 2 Areas By Vlookup ]

.


<table style="border-collapse: separate;" colspan="2" border="7" cellspacing="0"><tbody bgcolor="#ffffff"><tr height="52"><td colspan="2"> DataSheet= Sheet1
Let's Look & Find
[ Search Value In 2 Areas By Vlookup ] </td></tr><tr><td width="30" align="Right"><table style="table-layout: fixed; font-family: verdana;" rowspan="13" width="30" cellspacing="1"><tbody bgcolor="#ffffff"><tr height="18"><td></td></tr><tr height="18"><td align="Right" bgcolor="#dcdcdc">2 </td></tr><tr height="18"><td align="Right" bgcolor="#dcdcdc">3 </td></tr><tr height="18"><td align="Right" bgcolor="#dcdcdc">4 </td></tr><tr height="18"><td align="Right" bgcolor="#dcdcdc">5 </td></tr><tr height="18"><td align="Right" bgcolor="#dcdcdc">6 </td></tr><tr height="18"><td align="Right" bgcolor="#dcdcdc">7 </td></tr><tr height="18"><td align="Right" bgcolor="#dcdcdc">8 </td></tr><tr height="18"><td align="Right" bgcolor="#dcdcdc">9 </td></tr><tr height="18"><td align="Right" bgcolor="#dcdcdc">10 </td></tr><tr height="18"><td align="Right" bgcolor="#dcdcdc">11 </td></tr><tr height="18"><td align="Right" bgcolor="#dcdcdc">12 </td></tr><tr height="18"><td align="Right" bgcolor="#dcdcdc">13 </td></tr></tbody></table></td><td width="505"><table colspan="5" rowspan="6" style="table-layout: fixed; font-family: verdana;" width="505" bgcolor="#939393" border="0" cellspacing="1"><col width="83"><col width="105"><col width="85"><col width="83"><col width="105"><tbody bgcolor="#ffffff"><tr height="18"><td align="Center" bgcolor="#dcdcdc">A</td><td align="Center" bgcolor="#dcdcdc">B</td><td align="Center" bgcolor="#dcdcdc">C</td><td align="Center" bgcolor="#dcdcdc">D</td><td align="Center" bgcolor="#dcdcdc">E</td></tr><tr height="18"><td width="83" bgcolor="#ffffff">Name_1</td><td width="105" bgcolor="#ffffff">Addr</td><td width="85" bgcolor="#ffffff"> </td><td width="83" bgcolor="#ffffff">Name_2</td><td width="105" bgcolor="#ffffff">Addr</td></tr><tr height="18"><td width="83" bgcolor="#ffffff">Hong</td><td width="105" bgcolor="#ffffff">Busan</td><td width="85" bgcolor="#ffffff"> </td><td width="83" bgcolor="#ffffff">Hong2</td><td width="105" bgcolor="#ffffff">Busan2</td></tr><tr height="18"><td width="83" bgcolor="#ffffff">Kim</td><td width="105" bgcolor="#ffffff">Seoul</td><td width="85" bgcolor="#ffffff"> </td><td width="83" bgcolor="#ffffff">Kim2</td><td width="105" bgcolor="#ffffff">Seoul2</td></tr><tr height="18"><td width="83" bgcolor="#ffffff">Jang</td><td width="105" bgcolor="#ffffff">Jejoo</td><td width="85" bgcolor="#ffffff"> </td><td width="83" bgcolor="#ffffff">Jang2</td><td width="105" bgcolor="#ffffff">Jejoo2</td></tr><tr height="18"><td width="83" bgcolor="#ffffff">Lee</td><td width="105" bgcolor="#ffffff">DaeJeon</td><td width="85" bgcolor="#ffffff"> </td><td width="83" bgcolor="#ffffff">Lee2</td><td width="105" bgcolor="#ffffff">DaeJeon2</td></tr><tr height="18"><td width="83" bgcolor="#ffffff"> </td><td width="105" bgcolor="#ffffff"> </td><td width="85" bgcolor="#ffffff"> </td><td width="83" bgcolor="#ffffff"> </td><td width="105" bgcolor="#ffffff"> </td></tr></tbody></table><table colspan="5" rowspan="6" style="table-layout: fixed; font-family: verdana;" width="505" bgcolor="#939393" border="0" cellspacing="1"><col width="83"><col width="105"><col width="85"><col width="83"><col width="105"><tbody bgcolor="#ffffff"><tr height="18"><td width="83" bgcolor="#ffffff">hong</td><td width="105" bgcolor="#ccffcc">Busan</td><td width="85" bgcolor="#ffffff"> </td><td width="83" bgcolor="#ffffff"> </td><td width="105" bgcolor="#ffffff"> </td></tr><tr height="18"><td width="83" bgcolor="#ffffff">lee2</td><td width="105" bgcolor="#ccffcc">DaeJeon2</td><td width="85" bgcolor="#ffffff"> </td><td width="83" bgcolor="#ffffff"> </td><td width="105" bgcolor="#ffffff"> </td></tr><tr height="18"><td width="83" bgcolor="#ffffff">kim2</td><td width="105" bgcolor="#ccffcc">Seoul2</td><td width="85" bgcolor="#ffffff"> </td><td width="83" bgcolor="#ffffff"> </td><td width="105" bgcolor="#ffffff"> </td></tr><tr height="18"><td width="83" bgcolor="#ffffff">jang</td><td width="105" bgcolor="#ccffcc">Jejoo</td><td width="85" bgcolor="#ffffff"> </td><td width="83" bgcolor="#ffffff"> </td><td width="105" bgcolor="#ffffff"> </td></tr><tr height="18"><td width="83" bgcolor="#ffffff">wow</td><td width="105" bgcolor="#ccffcc">Nothongs</td><td width="85" bgcolor="#ffffff"> </td><td width="83" bgcolor="#ffffff"> </td><td width="105" bgcolor="#ffffff"> </td></tr><tr height="18"><td width="83" bgcolor="#ffffff">hmmm</td><td width="105" bgcolor="#ccffcc">Nothongs</td><td width="85" bgcolor="#ffffff"> </td><td width="83" bgcolor="#ffffff"> </td><td width="105" bgcolor="#ffffff"> </td></tr></tbody></table></td></tr></tbody></table>
<table style="border-collapse: separate;" border="6" cellspacing="0"><colspan=5 width="800" rowspan="3" height="54"></colspan=5><tbody><tr height="24"><td colspan="5" align="Center">Used Formula ...(With Running MicrosoftExcel Ver 2003)</td></tr><tr height="24"><td width="4%" align="Center" bgcolor="#d3d3d3">No</td><td width="9%" align="Center" bgcolor="#d3d3d3">Addr'</td><td width="65%" align="Center" bgcolor="#d3d3d3"> If use below Formula, You'll Get Result as Right</td><td width="15%" align="Center" bgcolor="#d3d3d3">Result</td><td align="Center" bgcolor="#d3d3d3">Etc</td></tr><tr height="20"><td align="Center">1</td><td align="Center" bgcolor="#ccffcc">B8</td><td bgcolor="#ccffcc">=IF(COUNTIF($A$2:$A$6,A8)＞0,VLOOKUP(A8,$A$2:$B$6,2,0),IF(COUNTIF($D$2:$D$6,A8)＞0,VLOOKUP(A8,$D$2:$E$6,2,0),"Nothongs"))</td><td>Busan</td><td> </td></tr><tr height="20"><td align="Center">2</td><td align="Center" bgcolor=""> </td><td>B8 His Formula Used This Cell -> B8:B13</td><td> </td><td> </td></tr><tr height="24"><td colspan="5">.</td></tr></tbody></table>