Multiple Result Vlookup

sbeatton

Active Member
Joined
May 19, 2004
Messages
411
Hello all

I have done a search of the board but to no avail. I am trying to wite a formula to lookup multiple values as per below. i.e. SOR Item 2 should be H11701. Is this possible.

Thanks,
Walksheet Enquiry List.xls
ABCD
3JobNumberMorethan1Answer?SORItem1SORItem2
490405468>1!H11700H11700
5
6
7JobNumberSorItemCode
890405468H11700
990405468H11701
Sheet1
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi SBeatton

I think this works:

Code:
=LOOKUP($A4&"@"&RIGHT(C$4,1),$A$8:$A$9&"@"&ROW(INDIRECT("1:"&ROWS($A$8:$A$9))),$B$8:$B$9)

Give it a shot - you will need to amend your ranges. If you have more than 9 occurrences, then the RIGHT formula will need to be amended.

Richard
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
I just put that there in case there were issues with one value being text, another numerical (converts 'em all to text).

The formula doesn't work though (at least it's not infallible) so you should look for anothwer solution.

Richard
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

using some helper cells, and putting the resulting items in a column, you can try this
   A          B         C  D                
 1 JOB NUMBER Item Code    3                
 2 1111       Item1        6                
 3 1111       Item2        Input JOB NUMBER 
 4 2222       Item3        2222             
 5 2222       Item4                         
 6 2222       Item5        Item Code        
 7 2222       Item6         Item3           
 8 3333       Item7         Item4           
 9 3333       Item8         Item5           
10 3333       Item9         Item6           

test

[Table-It] version 06 by Erik Van Geit
Code:
RANGE   FORMULA (1st cell)
D1      =MATCH(D4,A2:A10,0)
D2      =MATCH(D4,A2:A10,1)
D7:D10  =IF(ROWS($D$7:D7)<=($D$2-$D$1+1),INDEX(B$2:B$10,ROWS($D$7:D7)+$D$1-1),"")

[Table-It] version 06 by Erik Van Geit

I cannot find the thread, but it's somewhere on the board by Aladin Akyurek

kind regards,
Erik
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi sbeatton

A possible solution with an array formula:

In E2

Code:
=IF(COLUMNS($E$2:E2)>COUNTIF($A:$A,$D2),"",INDEX($B:$B,SMALL(IF($A$2:$A$15=$D2,ROW($A$2:$A$15)),COLUMNS($E$2:E2))))
This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.
Copy down and accross

Hope this helps
PGC
Book1
ABCDEFGHI
1JobNumberSorItemCodeJobNumberSORIt1SORIt2SORIt3SORIt4
290405468H1170090405468H11700H11701  
390405468H1170190405469H11702   
490405469H1170290405470H11703H11708H11709H11713
590405470H1170390405471H11704   
690405471H1170490405472H11705H11710H11711 
790405472H1170590405473H11706H11712  
890405473H1170690405474H11707   
990405474H11707
1090405470H11708
1190405470H11709
1290405472H11710
1390405472H11711
1490405473H11712
1590405470H11713
16
Sheet3
 

Forum statistics

Threads
1,136,990
Messages
5,678,985
Members
419,797
Latest member
ikethegenius

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