vlookup formula to shows 2nd , 3rd, 4th result for same arrey value.

A

Amit Desai

New Member
Joined
Mar 21, 2020
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
Namste🙏,

i have table of data where one product code have multiple values. is there any way to lookup for all the available values in column. VLOOKUP is bringing only first match value. for your ready reference i am giving you sample data. if some expert can help me to get my desired result i will be very thankful. ( should support office version 2016 and above )

Book11.xlsx
ABCDEFGHIJ
1Item CodeUnitDATEM/CShiftC&AOK CountRej Countvlookup formulaexpected result
21102001059P-145447775TA775T110200105900775T1102001059
31102001059P-145447775TB775T110200105900775T1102001059
41102000332P-145447660T.1A660T.111020003322483660T.11102000332
51102000332P-145447660T.1B660T.111020003323210660T.11102000332
61102001433P-145447660T.2A660T.211020014338312660T.21102001433
71102000690P-145447660T.2A1660T.211020006908611660T.21102000690
81102000690P-145447660T.2B660T.211020006905232660T.21102000690
91102000039P-145447450T.1A450T.11102000039982450T.11102000039
101102000039P-145447450T.1B450T.111020000391280450T.11102000039
111102000363P-145447350T.1A350T.111020003632362350T.11102000363
121102000363P-145447350T.1B350T.111020003633442350T.11102000363
131102001440P-145447350T.2A350T.2110200144080658350T.21102001440
141102001440P-145447350T.2B350T.21102001440124050350T.21102001440
151102001284P-145447350T.3A350T.311020012844456216350T.31102001284350T.31102001284
161102001284P-145447350T.2B350T.211020012848090174350T.31102001284350T.21102001284
171102001284P-145447350T.4B350T.411020012845230250350T.31102001284350T.41102001284
181102001037P-145447250T.1A250T.1110200103727812570250T.11102001037
191102001037P-145447250T.1B250T.1110200103739254168250T.11102001037
201102001287P-145447250T.4A250T.41102001287719949250T.41102001287
Sheet3
Cell Formulas
RangeFormula
I2:I20I2=VLOOKUP(A2,$A$2:$F$52,6,0)
 
Sort by date Sort by votes

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Amit Desai said:
giving name error
Click to expand...
That's because you changed the formula, that said it should be
Excel Formula: 
=INDEX('DPR_Link-DT'!G:G,AGGREGATE(15,6,ROW('DPR_Link-DT'!$A$2:$A$5100)/('DPR_Link-DT'!$A$2:$A$5100=A414),COUNTIFS(A$414:A414,A414)))
 
Upvote 0
hi, let me make it easy by adding both sheet data as i am unable to generate desire result which you guyes brought very easily. not sure where i am making mistake.

Book1
ABCDE
1Output ProductInput ProductInput QuantityProduct_MatchedMC+SFG
21102000037100100196888.2 
3110200003710010004541087.8 
41102000039100100172528071102000039
5110200003911990000024011102000039
6110200033110010002300.8808 
711020003311001001806366.5596 
8110200033210010002313419.641102000332
91102000332100100038057.961102000332
1011020003321199000052386.41102000332
11110200128210010016890.0625 
12110200128310010016890.0625 
131102001284100100004521.251102001284
141102001284100100024939.11102001284
151102001284100100026019.551102001284
16110200128410010002615.11102001284
17110200128410010005681.71102001284
1811020012841001000676297.51102001284
191102001284119900031940.81102001284
BOM_Amit-DND
Cell Formulas
RangeFormula
A2:A5A2='[Material Reco-Jun-24 (Plant-1) -04.06.2024 (2).xlsx]New_Bom'!C413
B2:B5B2='[Material Reco-Jun-24 (Plant-1) -04.06.2024 (2).xlsx]New_Bom'!E413
C2:C5C2='[Material Reco-Jun-24 (Plant-1) -04.06.2024 (2).xlsx]New_Bom'!G413
D2:D19D2=IFERROR(VLOOKUP($A2,'DPR_Link-DT'!$A$2:$O$995,1,0),"")
A6:A10A6='[Material Reco-Jun-24 (Plant-1) -04.06.2024 (2).xlsx]New_Bom'!C499
B6:B10B6='[Material Reco-Jun-24 (Plant-1) -04.06.2024 (2).xlsx]New_Bom'!E499
C6:C10C6='[Material Reco-Jun-24 (Plant-1) -04.06.2024 (2).xlsx]New_Bom'!G499
A11:A19A11='[Material Reco-Jun-24 (Plant-1) -04.06.2024 (2).xlsx]New_Bom'!C2231
B11:B19B11='[Material Reco-Jun-24 (Plant-1) -04.06.2024 (2).xlsx]New_Bom'!E2231
C11:C19C11='[Material Reco-Jun-24 (Plant-1) -04.06.2024 (2).xlsx]New_Bom'!G2231


Book1
ABCDEF
1Item CodeUnitDATEM/CShiftlookup value
21102001059P-145447775TA775T1102001059
31102001059P-145447775TB775T1102001059
41102000332P-145447660T.1A660T.11102000332
51102000332P-145447660T.1B660T.11102000332
61102001433P-145447660T.2A660T.21102001433
71102000690P-145447660T.2A1660T.21102000690
81102000690P-145447660T.2B660T.21102000690
91102000039P-145447450T.1A450T.11102000039
101102000039P-145447450T.1B450T.11102000039
111102000363P-145447350T.1A350T.11102000363
121102000363P-145447350T.1B350T.11102000363
131102001440P-145447350T.2A350T.21102001440
141102001440P-145447350T.2B350T.21102001440
151102001284P-145447350T.3A350T.31102001284
161102001284P-145447350T.3B350T.31102001284
171102001284P-145447350T.4B350T.41102001284
181102001037P-145447250T.1A250T.11102001037
191102001037P-145447250T.1B250T.11102001037
201102001287P-145447250T.4A250T.41102001287
211102001287P-145447250T.4B250T.41102001287
221102001265P-145447250T.5A250T.51102001265
231102001265P-145447250T.5B250T.51102001265
241102000634P-145447200T.1A200T.11102000634
251102000634P-145447200T.1B200T.11102000634
261102000950P-145447200T.3A200T.31102000950
271102000950P-145447200T.3B200T.31102000950
281102000823P-145447180TA180T1102000823
291102000823P-145447180TB180T1102000823
301102001232P-145447160T.1A160T.11102001232
311102001232P-145447160T.1B160T.11102001232
321102001048P-145447160T.2A160T.21102001048
331102001048P-145447160T.2B160T.21102001048
341102001180P-145447125T.2A125T.21102001180
351102001180P-145447125T.2B125T.21102001180
361102001201P-145447125T.5A125T.51102001201
371102001201P-145447125T.5B125T.51102001201
381102001264P-145447120T.1A120T.11102001264
391102001264P-145447120T.1B120T.11102001264
401102000638P-145447120T.2A120T.21102000638
411102000639P-145447120T.2A120T.21102000639
421102000638P-145447120T.2B120T.21102000638
431102000639P-145447120T.2B120T.21102000639
441102001297P-145447120T.2B1120T.21102001297
451102001235P-145447120T.3A120T.31102001235
461102001235P-145447120T.3B120T.31102001235
471102001387P-145447100TA100T1102001387
481102001387P-145447100TB100T1102001387
491102000637P-14544780T.A80T.1102000637
501102000637P-14544780T.B80T.1102000637
511102001363P-14544760TA60T1102001363
521102001363P-14544760TB60T1102001363
DPR_Link-DT



i need that forumla should get all available values from DPR_Link-DT F colum where it matchs with item code in "BOM_Amit-DND"

sorry if i made you guys confused for my requirement :(
 
Upvote 0
Cubist said:
What does this mean?
Does your data start in row 2 or 414?
Click to expand...
there are 3000+ line items and in the row 414 my first code matched with DPR_Link-DT file show i wrote formula there to check if i am getting desired result or not. to omit the confussion i have add both sheet sample data if that can help you to prepare formula that match my requirements. i am very much thankful to all of you who are giving your time and efforts. god bless you all. :)
 
Upvote 0
How about
Fluff.xlsm
ABCDE
1Output ProductInput ProductInput QuantityProduct_MatchedMC+SFG
21102000037100100196888.2 
3110200003710010004541087.8 
41102000039100100172528071102000039450T.11102000039
5110200003911990000024011102000039450T.11102000039
6110200033110010002300.8808 
711020003311001001806366.5596 
8110200033210010002313419.641102000332660T.11102000332
91102000332100100038057.961102000332660T.11102000332
1011020003321199000052386.41102000332 
11110200128210010016890.0625 
12110200128310010016890.0625 
131102001284100100004521.251102001284350T.31102001284
141102001284100100024939.11102001284350T.31102001284
151102001284100100026019.551102001284350T.41102001284
16110200128410010002615.11102001284 
17110200128410010005681.71102001284 
1811020012841001000676297.51102001284 
191102001284119900031940.81102001284 
Data
Cell Formulas
RangeFormula
E2:E19E2=IFERROR(INDEX('DPR_Link-DT'!F:F,AGGREGATE(15,6,ROW('DPR_Link-DT'!$A$2:$A$5000)/('DPR_Link-DT'!$A$2:$A$5000=A2),COUNTIFS(A$2:A2,A2))),"")
 
Upvote 0
Solution
Fluff said:
How about
Fluff.xlsm
ABCDE
1Output ProductInput ProductInput QuantityProduct_MatchedMC+SFG
21102000037100100196888.2 
3110200003710010004541087.8 
41102000039100100172528071102000039450T.11102000039
5110200003911990000024011102000039450T.11102000039
6110200033110010002300.8808 
711020003311001001806366.5596 
8110200033210010002313419.641102000332660T.11102000332
91102000332100100038057.961102000332660T.11102000332
1011020003321199000052386.41102000332 
11110200128210010016890.0625 
12110200128310010016890.0625 
131102001284100100004521.251102001284350T.31102001284
141102001284100100024939.11102001284350T.31102001284
151102001284100100026019.551102001284350T.41102001284
16110200128410010002615.11102001284 
17110200128410010005681.71102001284 
1811020012841001000676297.51102001284 
191102001284119900031940.81102001284 
Data
Cell Formulas
RangeFormula
E2:E19E2=IFERROR(INDEX('DPR_Link-DT'!F:F,AGGREGATE(15,6,ROW('DPR_Link-DT'!$A$2:$A$5000)/('DPR_Link-DT'!$A$2:$A$5000=A2),COUNTIFS(A$2:A2,A2))),"")
Click to expand...
Yes it worked :) thanks a lot for your kind support and time 🙏 .
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Vlookuping matching values with hidden rounding errors
Replies
5
Views
506
awoohaw
awoohaw
O
  • Question Question
Vlookup() and Index(Match()) returning N/A, even though the lookup value exists in the range and is identical to that value within the range.
Replies
6
Views
2K
nemmi69
nemmi69
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
198
Pbobas
P
M
  • Question Question
lookup help - extracting values not from the first match in a dataset
2
Replies
11
Views
833
bebo021999
B
J
  • Solved
Return column header for highest, 2nd highest, 3rd highest etc value
Replies
4
Views
671
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

Share this page

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