VLookup - One lookup value - multiple results

[thassos96]

Hello,
we have a problem with one of our files. The file contains of numbers and a corresponding failure reason. Sometimes one number can have up to 3 different failure reasons. If I use Vlookup and take the numbers as lookup value the results will only show one failure reason (the first one that appears in the list). Is there a way to show all failure reason for one number.
Thanks.

 

[hiker95]

thassos96,

Welcome to the MrExcel forum.


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum.

To attach screenshots, see below: Post a screen shot with one of these:

If you are not able to give us screenshots, see below: You can upload your workbook to Box Net

 

[thassos96]

Hello Hiker95,
Thanks for your reply. We are using Excel 2003 SP3.
We receive the report every day and comparing the data from today with the results from yesterday to see if anything new comes.
<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; width: 48pt;" height="17" width="64">Number </td> <td class="xl22" style="border-left: medium none; width: 48pt;" width="64">Failure</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17">4</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">FR1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17">4</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">FR2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17">4</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">FR3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17">5</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">FR2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17">5</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">FR3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17">3</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">FR1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17">3</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">FR2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">FR1</td> </tr> </tbody></table>using formula
=VLOOKUP(H3,$A$3:$B$10,2,0)
Results in this
<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" align="left" height="17" width="64">Number</td> <td class="xl24" style="border-left: medium none; width: 48pt;" align="left" width="64">Failure</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" height="17">5</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" align="left">FR2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" height="17">3</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" align="left">FR1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" height="17">3</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" align="left">FR1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" height="17">4</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" align="left">FR1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" height="17">5</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" align="left">FR2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" height="17">4</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" align="left">FR1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" height="17">1</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" align="left">FR1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" height="17">4</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" align="left">FR1</td> </tr> </tbody></table>I hope this makes sense
Thanks.