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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

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