# help with array lookup formula

#### Ralph Gregory

##### Board Regular
Hi All, I have the following formula to find all rows of data on a sheet Jobs! in column B that matches text from Cell C7 in sheet1. Jobs! sheet has a header row and is of unknown size as the data is refreshed from odbc
The formula works for the first cell it is used in but every cell after it brings up #NUM error...what am I doing wrong?
=SMALL(IF(Jobs!\$A\$2:\$B\$65536=OFFSET(Jobs!\$B\$1,MATCH(\$C\$7,Jobs!\$B\$2:\$B\$65536,0),0),ROW(Jobs!\$A\$2:\$B\$65536)),ROW(1:1))

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### Lewiy

##### Well-known Member
Try this, I don't know if it will solve your problem as I don't know what your data is like:
Code:
``=SMALL(IF(Jobs!\$A:\$B=OFFSET(Jobs!\$B\$1,MATCH(\$C\$7,Jobs!\$B:\$B,0),0),ROW(Jobs!\$A:\$B)),ROW(1:1))``

#### Ralph Gregory

##### Board Regular
Hi Lewiy,
That gives me the #num! error on the first row so I don't quite know whats happening

#### Lewiy

##### Well-known Member
It was just a guess really, can you post an example of your data and the results you expect to get?

#### Ralph Gregory

##### Board Regular
Here is a small selection of Jobs! sheet if C7 = Ableman* I would expect the formula to return the row numbers 6 7 8 9 10 11 12 on consecutive rows on sheet 1 that the formula was pasted into..even more useful if it returned the relevant job numbers and names but that is a further step once I get this working

JOB_NUMBER JOB_DESCRIPTION
B61340 Mr Abbot,replace dormer front
B61494 Mr G Abbot, build double story
B61658 Mr Abbott.Build Extension.
H52621 Mr Graham Abbott,flush heating
H50166 Ableman.Milton Keynes.Plumbing
H50648 Ableman Builders Install
H50677 Ableman Plumbing & heating to
H50699 Ableman Ltd,Heating+Plumbing
H51403 Ableman Ltd,heating+plumbing
H51481 Ableman Ltd,repair leak in fie
B61253 Mat Abstreigh,30 sheets
BA53 Abundant Grace Rechargeable
HA53 H & P :Recharges:Abundant
PA53 Abundant Grace Painting Rechar
RABU1 Recharges - Abundant Grace
H51287 Access International,pilot lig
H51567 Access International.Call Out

#### Lewiy

##### Well-known Member
On sheet2 I would do this:
Excel Workbook
ABC
10JOB_NUMBERJOB_DESCRIPTION
21B61340Mr Abbot,replace dormer front
32B61494Mr G Abbot, build double story
43B61658Mr Abbott.Build Extension.
54H52621Mr Graham Abbott,flush heating
75H50166Ableman.Milton Keynes.Plumbing
85H50648Ableman Builders Install
95H50677Ableman Plumbing & heating to
105H50699Ableman Ltd,Heating+Plumbing
115H51403Ableman Ltd,heating+plumbing
125H51481Ableman Ltd,repair leak in fie
145B61253Mat Abstreigh,30 sheets
155BA53 Abundant Grace Rechargeable
165HA53 H& P :Recharges:Abundant
175PA53 Abundant Grace Painting Rechar
185RABU1 Recharges - Abundant Grace
195H51287Access International,pilot lig
205H51567Access International.Call Out
Sheet2

And on sheet1 I would do this:

Hope that helps!

#### Ralph Gregory

##### Board Regular
Brilliant !! a bit of modification and it suits me fine thanks
now if I was to select one of those resulting job numbers how could I get that to populate a cell say \$A\$12 on result sheet

#### Lewiy

##### Well-known Member
Which resulting job numbers do you mean and how do you propose "selecting" them?

#### Ralph Gregory

##### Board Regular
Hi Lewiy,
in the sample you show row 10 returns
H52621 Mr Graham Abbott,flush heating
I want to select the cell with H52621 in it and for that number to copy into Front!\$F\$13 (that then shows me all the info I have on that Job number)

#### Lewiy

##### Well-known Member
Right click the worksheet tab of the sheet with the results in and select "View Code". Paste this code into the screen that comes up:
Code:
``````Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("Front").Range("F13") = Target.Value
End Sub``````
Now whichever cell is selected on that sheet, it's value will appear in Front!\$F\$13

Replies
7
Views
308
Replies
4
Views
282
Replies
0
Views
203
Replies
7
Views
495
Replies
3
Views
1K

1,190,647
Messages
5,982,116
Members
439,756
Latest member
alice128

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