# Lookup Value in an Array

#### PaulGotts

##### New Member
Hello All, need assistance here... a real brain buster for me.

Using the array below, how would I create a formula that finds and returns a cell or column reference?

i.e. via a formula, I want to find the column which contains "Andy" and "124" (there may be duplicate values in the array).
 Andy Brian Andy Andy Brian Andy Brian Brian Andy Brian Brian Andy Brian N O P 117 112 102 8 3 25 417 419 11 35 47 20 429 420 440 119 14 7 31 124 63 76 21 18 9 121 406 44 21 12 53 28 26 56 29 31 58 29 33 110 36 34 403 38 42 405 38 43 412 50 49 414 52 50 416 60 57 434 61 59 435 64 62 437 77 69 446 80 70 82 71 86 81 87 82 89 100 91 103 96 132 97 135 98 406 99 413 103 415 111 416 111 419 118 421 124 429 127 431 401 432 402 437 422 441

<TBODY>
</TBODY>

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Firefly2012

##### Well-known Member
Hi

Perhaps this:

Excel 2007
ABCDEFGHIJKLMNOPQR
1AndyBrianAndyAndyBrianAndyBrianBrianAndyBrianBrianAndyBrianNOPAndy
2117112102832541741911354720429420440124
3119147311246376214
418912140644
5211253
6282656
7293158
82933110
93634403
103842405
113843412
125049414
135250416
146057434
156159435
166462437
177769446
188070
198271
208681
218782
2289100
2391103
2496132
2597135
2698406
2799413
28103415
29111416
30111419
31118421
32124429
Sheet1
Cell Formulas
RangeFormula
R3{=MIN(IF(\$A\$1:\$P\$1=R1,IF(COUNTIF(OFFSET(\$A\$2:\$A\$36,0,COLUMN(\$A\$1:\$P\$1)-COLUMN(\$A\$1)),R2)>0,COLUMN(\$A\$1:\$P\$1)-COLUMN(\$A\$1)+1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

#### PaulGotts

##### New Member
Once adjusted for my application this has worked wonderfully. Thank you very much.

#### Firefly2012

##### Well-known Member
Paul, you're welcome

Replies
4
Views
1K
Replies
3
Views
234
Replies
1
Views
77
Replies
3
Views
74
Replies
3
Views
240

1,195,680
Messages
6,011,126
Members
441,586
Latest member
rodsin76

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