VLookup - One lookup value - multiple results

thassos96

New Member
Joined
Mar 29, 2011
Messages
2
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.
moz-screenshot.png
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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

New Member
Joined
Mar 29, 2011
Messages
2
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,710
Messages
5,833,247
Members
430,200
Latest member
ADLHMA2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top