Wrong data type error in a simple array formula

[DavidSCowan]

Could somebody help please this is driving me mad! I am trying to use FIND and an array formula to find the position of text in a range of cells (A2 and A3 in the example) which could be one of a number of options (C1:D1 here). But the array formula throws up the following error: "A value used in the formula is of the wrong data type". The simplest illustration of the problem is as follows. The formula in B2 is

{=FIND(($C$1:$D$1),A2)}

and $C$1:$D$1 contain REF and ATM respectively. [/CODE][TABLE="width: 562"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD] REF[/TD]
[TD] ATM[/TD]
[/TR]
[TR]
[TD]203047 05AUG 08.55 OKEHAMPTON ATM[/TD]
[TD="align: center"]#VALUE![/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CO-OP GROUP 380611 REF 191 7553375222 BCC[/TD]
[TD="align: right"]22[/TD]
[TD] [/TD]
[TD]We see that B2 has a #VALUE! error - wrong data type. But for some reason B3 is ok returning 22! Any thoughts greatly appreciated. PS for some reason I can't type this properly!![/TD]
[/TR]
</tbody><colgroup><col><col span="3"></colgroup>[/TABLE]

 

[shift-del]

Hello

You were almost there.

<colgroup><col style="width: 28ptpx"><col width="221,25pt"><col width="40,5pt"><col width="60pt"><col width="60pt"></colgroup><tbody>
[TD="colspan: 5, align: center"]Arbeitsblatt mit dem Namen 'Tabelle1'[/TD]

[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]

[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: left"]REF[/TD]
[TD="align: left"]ATM[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]203047 05AUG 08.55 OKEHAMPTON ATM[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]CO-OP GROUP 380611 REF 191 7553375222 BCC[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]

</tbody>

Zelle	Formel
B2	=LOOKUP(42,1/FIND($C$1:$D$1,A2),FIND($C$1:$D$1,A2))

<colgroup><col style="width: 40ptpx"><col></colgroup><tbody>
</tbody>

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>

 

[DavidSCowan]

Hi there, thank you very much for this and it works a treat - great stuff! For my education I would really like to understand why my simpler formula didn't work could you explain please. Thanks again for the solution. Rgds David

 

[shift-del]

Hello David

FIND($C$1:$D$1,A2) returns an array of two values. Highlight the formula and press F9.
For B2 {#VALUE!.31}.
For C2 {20.#VALUE!}.
You just have to grab the number value.

 

[DavidSCowan]

Great thank you very much. I am not as practiced at using array formulae as I would like to be this will get me using them more. Thanks again - really helpful.

 

[Aladin Akyurek]

B2:

=LOOKUP(9.99999999999999E+307,SEARCH($C$1:$D$1,A2),$C$1:$D$1)

If you want it case-sensitive, replace SEARCH with FIND.

 

[Yahya]

Hello

You were almost there.

<tbody>
[TD="colspan: 5, align: center"]Arbeitsblatt mit dem Namen 'Tabelle1'[/TD]

[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]

[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]REF[/TD]
[TD="align: left"]ATM[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]203047 05AUG 08.55 OKEHAMPTON ATM[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]CO-OP GROUP 380611 REF 191 7553375222 BCC[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Zelle	Formel
B2	=LOOKUP(42,1/FIND($C$1:$D$1,A2),FIND($C$1:$D$1,A2))

<tbody>
</tbody>

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>

hi shift-del
in your formula why you use 42
you just need 2

<tbody>
</tbody>

 

[shift-del]

Hello Yahya

hi shift-del in your formula why you use 42
you just need 2

<tbody>
</tbody>

It's my trademark.

 

[DavidSCowan]

[TABLE="width: 74"]
<tbody>[TR]
[TD]We could also have used MAX(IF(ISERROR(FIND($C$1:$D$1,A9)),0,FIND($C$1:$D$1,A2))) although not as elegant as the LOOKUP version. But ...shift-del would need to find a way to bring in his trademark!
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

[TABLE="width: 74"]
<tbody>[TR]
[TD]We could also have used MAX(IF(ISERROR(FIND($C$1:$D$1,A9)),0,FIND($C$1:$D$1,A2))) although not as elegant as the LOOKUP version. But ...shift-del would need to find a way to bring in his trademark!
[/TD]
[/TR]
</tbody>[/TABLE]

Lookup without a division is faster...