help with array lookup formula

[Ralph Gregory]

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

 

[Lewiy]

Try this, I don't know if it will solve your problem as I don't know what your data is like:

=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]

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

 

[Lewiy]

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

 

[Ralph Gregory]

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
H52669 Mr Graham Abbott,replace radia
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
H52344 Ableman Ltd,Roade Library,heat
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]

On sheet2 I would do this:

Excel Workbook
	A	B	C
1	0	JOB_NUMBER	JOB_DESCRIPTION
2	1	B61340	Mr Abbot,replace dormer front
3	2	B61494	Mr G Abbot, build double story
4	3	B61658	Mr Abbott.Build Extension.
5	4	H52621	Mr Graham Abbott,flush heating
6	5	H52669	Mr Graham Abbott,replace radia
7	5	H50166	Ableman.Milton Keynes.Plumbing
8	5	H50648	Ableman Builders Install
9	5	H50677	Ableman Plumbing & heating to
10	5	H50699	Ableman Ltd,Heating+Plumbing
11	5	H51403	Ableman Ltd,heating+plumbing
12	5	H51481	Ableman Ltd,repair leak in fie
13	5	H52344	Ableman Ltd,Roade Library,heat
14	5	B61253	Mat Abstreigh,30 sheets
15	5	BA53 Ab	undant Grace Rechargeable
16	5	HA53 H	& P :Recharges:Abundant
17	5	PA53 Ab	undant Grace Painting Rechar
18	5	RABU1 R	echarges - Abundant Grace
19	5	H51287	Access International,pilot lig
20	5	H51567	Access International.Call Out
Sheet2

And on sheet1 I would do this:

Excel Workbook
	A	B	C	D	E
4
5
6
7			abbot	B61340	Mr Abbot,replace dormer front
8				B61494	Mr G Abbot, build double story
9				B61658	Mr Abbott.Build Extension.
10				H52621	Mr Graham Abbott,flush heating
11				H52669	Mr Graham Abbott,replace radia
12				#N/A	#N/A
13				#N/A	#N/A
14				#N/A	#N/A
15				#N/A	#N/A
16				#N/A	#N/A
17				#N/A	#N/A
18				#N/A	#N/A
19				#N/A	#N/A
20				#N/A	#N/A
Sheet1

Hope that helps!

 

[Ralph Gregory]

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]

Which resulting job numbers do you mean and how do you propose "selecting" them?

 

[Ralph Gregory]

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]

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:

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