# Multiple Result Vlookup

#### sbeatton

##### Active Member
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
490405468>1!H11700H11700
5
6
7JobNumberSorItemCode
890405468H11700
990405468H11701
Sheet1

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### Richard Schollar

##### MrExcel MVP
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

#### bsnapool

##### Active Member
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

##### MrExcel MVP
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
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
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

Replies
3
Views
96
Replies
1
Views
61
Replies
10
Views
156
Replies
9
Views
101
Replies
1
Views
114

1,170,931
Messages
5,872,774
Members
432,944
Latest member
mj02

### 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.

### Which adblocker are you using?

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

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