Multiple Result Vlookup

[sbeatton]

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
	A	B	C	D
3	JobNumber	Morethan1Answer?	SORItem1	SORItem2
4	90405468	>1!	H11700	H11700
5
6
7	JobNumber	SorItemCode
8	90405468	H11700
9	90405468	H11701
Sheet1

 

[Richard Schollar]

Hi SBeatton

I think this works:

=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

 

[bsnapool]

Richard

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

What does the @ symbol do???

 

[Richard Schollar]

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]

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

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]

Hi sbeatton

A possible solution with an array formula:

In E2

=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
	A	B	C	D	E	F	G	H	I
1	JobNumber	SorItemCode		JobNumber	SORIt1	SORIt2	SORIt3	SORIt4
2	90405468	H11700		90405468	H11700	H11701
3	90405468	H11701		90405469	H11702
4	90405469	H11702		90405470	H11703	H11708	H11709	H11713
5	90405470	H11703		90405471	H11704
6	90405471	H11704		90405472	H11705	H11710	H11711
7	90405472	H11705		90405473	H11706	H11712
8	90405473	H11706		90405474	H11707
9	90405474	H11707
10	90405470	H11708
11	90405470	H11709
12	90405472	H11710
13	90405472	H11711
14	90405473	H11712
15	90405470	H11713
16
Sheet3