Vlookup only pulling first example of duplicates in a column

christianna

New Member
Joined
Jul 20, 2008
Messages
6
I have a file with 4 columns and about 21 rows (Excel 2010). The first table in the sheet holds number of occurances in column A and then column B is the name of the occurance. In column E I use the Large function (=LARGE($A$4:$A$23,1), =LARGE($A$4:$A$23,2), etc.) in each of the rows to create a descending list of the occurances (creating a Pareto diagram). In column D, I am trying to pull in the corresponding name of each occurance using the Vlookup command (=VLOOKUP(E4,$A$4:$B$21,2,FALSE), =VLOOKUP(E5,$A$4:$B$21,2,FALSE), etc). The problem is that when there are multiple occurances with the same number of occurances (15 missing information, 15 stockouts) it is only pulling the first name of the occurance into the D column.

So, if there's a number 15 in A4 with the occurance name of missing info in B4 and a number 15 in A5 with the occurance name of stockouts in B5, the E column is right, and D4 is right (shows missing information), but D5 doesn't show stockouts, it repeats missing information. Hope that makes sense. Here's a summary of the cells:

A4 = 15
B4 = missing info
A5 = 15
B5 = stockouts
D4 = =LARGE($A$4:$A$23,1) ***15 is the largest number in the list
E4 = =VLOOKUP(E4,$A$4:$B$21,2,FALSE)
D5 = =LARGE($A$4:$A$23,2)
E5 = =LARGE($A$4:$A$23,2)

Since I'm trying to use this info to create a Pareto diagram, I need the name of the occurance to be accurate. Is there a better way to do this or how can I make this work?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
christianna,

You may be able to use User Defined Function VLookupNth, or and array formula.

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.

Please attach screenshots of your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet.

This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or
RichardSchollar’s beta HTML Maker -...his signature block at the bottom of his post

or
Borders-Copy-Paste


Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.
 
Upvote 0
Thank you. I apologize for not providing screen shots. I typically try to do that where I can, but I can't seem to get any of the programs to work that will allow me to do screenshots (maybe because I am using Excel 2010 now?). I uploaded the file to http://www.box.net/shared/7pgpge2btj. There are several tabs. The tab that has the data is CS Week Data for Pareto. The Pareto chart that is using the data is on another worksheet labeled Pareto Chart. You will be able to see what it is doing (duplicating the first name it sees when there are duplicate values) by looking at the x axis on the chart. I'm guessing there's something simple that can be used to do what I'm trying to do and I'm just not advanced enough to know. Your help is appreciated.
 
Upvote 0
christianna,

Thank you for the workbook.

I can not see/understand/come up with a solution for what you are trying to accomplish.

Click on the Post Reply button, and just enter the word BUMP, and then click on the Submit Reply button, and someone else will assist you.
 
Upvote 0
Try in D4; copy down,

Array Formula, Conformed with CTRL+SHIFT+ENTER,

=INDEX($B$4:$B$23,SMALL(IF($A$4:$A$23=E4,ROW($A$4:$A$23)-MIN(ROW($A$4:$A$23))+1),COUNTIF(E$4:E4,E4)))
 
Upvote 0
This is an Array Formula, must hit
Excel Workbook
DE
2Defect Types/ Event OccurrenceTOTAL
3
4Interruptions (someone wanting info)34
5Software (not working)28
6Missing Parts (don't know why)15
7Changeovers (unscheduled)15
8Missing Info (what to work on)12
9Documentation (not there)12
10Unkitted Parts (here not pulled)9
11Documentation (not accurate)7
12Consumables not available7
13Searching for tools6
14Expedite - facilities5
15Expedite - orders5
16Documentation (not complete)3
17Stock-outs (parts not ordered)2
18Documentation (not available )2
19Unscheduled Meeting1
20Defective Parts / Material1
CTRL+SHIFT+ENTER, not just ENTER This is the output i got, D4 copy down CS Week Data for Pareto
Excel 2007
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself


Is this do you mean?
 
Upvote 0
I thought I was hitting that, but I wasn't. User error. :oops:

Thank you soooo much. I would have NEVER gotten that one fixed on my own.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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
Back
Top