Index/Matching area arrays, or searching array for specific number

[bcwilliam87]

Is it possible to search a (for ex.) 10x by 10y array of numbers for a specific number? I have only accomplished this by stringing together a: If(Iserror(index rows A15:J15)... true=index(row A16:J16), false=index rows A15:J15)...etc, extending the formula row by row until finding the specific number using IF statements...

You can imagine this formula getting bulky in a 10x10 array. I would like to search the 10x10 array for my number rather than taking it row by row like that.

To make it more complicated...

To complete the formula I then MATCH() the INDEX() in another 10x10 array (just like the first). This allows me to search one 10x10 array and return the value of a second 10x10 array.

My formula looks like this:

=IF(ISERROR(INDEX($G$20:$R$20,MATCH(S$38&$K39,$G$5:$R$5,0))),IF(ISERROR(INDEX($G$21:$R$21,MATCH(S$38&$K39,$G$6:$R$6,0))) ... If false--so it does find my number, then repeat formula: (INDEX($G$20:$R$20,MATCH(S$38&$K39,$G$5:$R$5,0)), INDEX($G$21:$R$21,MATCH(S$38&$K39,$G$6:$R$6,0))

It's not pretty. These are my arrays and outputs

First 10x10 array

REF1 1 2 3 4 5 6 7
GND 8 9 10 11 12 13 14 15 16
REF2 97 33 34 35 36 37 38 39 17 18 19
98 65 66 67 40 41 42 43 44 45 20 21
99 100 68 69 70 46 47 48 49 50 22 23
101 102 71 72 73 74 51 52 53 54 24 25
103 104 75 76 77 78 55 56 57 58 26 27
105 106 79 80 81 82 83 59 60 61 28 29
107 108 84 85 86 87 88 89 90 62 30 31
109 110 111 91 92 93 94 95 96 63 64 32
112 113 114 115 116 117 118 119 120 GND
121 122 123 124 125 126 127 128




Second 10x10 Array
REF1 D30 D28 D26 D24 D22 D20 D18
GND D32 D31 D29 D27 D25 D23 D21 D19 D16
REF2 C31 A32 A30 A28 A26 A24 A22 A18 D17 D15 D14
C32 B31 B29 B32 A31 A29 A27 A20 A16 A14 D13 D12
C30 C29 B27 B28 B30 A21 A25 A23 A12 A10 D11 D10
C28 C27 B25 B26 B20 B22 A19 A13 A11 A8 D9 D8
C26 C25 B23 B24 B14 B18 A15 A17 A9 A6 D7 D6
C24 C23 B21 B19 B12 B10 B16 A5 A7 A4 D5 D4
C21 C22 B17 B15 B11 B8 B6 B4 B2 A3 D2 D3
C19 C20 C18 B13 B9 B7 B5 B3 B1 A1 A2 D1
C17 C16 C14 C12 C10 C8 C6 C4 C2 GND
C15 C13 C11 C9 C7 C5 C3 C1



Output

INDEX Array 1, MATCH array 2 based on specific number such as "A2."

I do have this working as it is but it is uuugly. Just knowing how to search arrays like this would be helpful...

Thanks!

[bcwilliam87]

and the arrays looked better when I wrote this. But any block of data would do...

[jhren]

See cell A14 in attached example. Note it's an array formula which requires Ctrl+Shift+Enter for confirmation (as opposed to Enter for regular formulas).

[bcwilliam87]

Very cool thank you so much!!

[bcwilliam87]

Quick question -- I am a bit new at array formula and how they work. Is it possible to locate the array somewhere else than A1? Each time I try changing rows or columns from A1:J10 to something like B2:K10 the formula returns a different value... Maybe I'm doing something weird...

[djapigo]

If you change to B2:K10, make sure all are changed to that range...

Also, make sure after your edit the formula... you do not just use ENTER... you have to use CTRL-SHIFT-ENTER...

In other words, press F2 (to be in Edit mode) then CTRL-SHIFT-ENTER

You know it's right if you see the magic curly brackets...

[djapigo]

Sorry... try this instead... make sure you use CTRL-SHIFT-ENTER...

=INDEX(B2:K10,SMALL(IF(B2:K10=A13,ROW(B2:K10)-1),1),SMALL(IF(B2:K10=A13,COLUMN(B2:K10)-1),1))

The difference is the "-1" after the ROW() and COLUMN()...

Apologies to jhren for replying back on your thread... kudos to your great formula!

[jhren]

No problemo!

You are correct. When the array is otherwise located, offsetting (as with your -1's) for the top left array cell's location is one method.