Wildcard issue for Index/Match Function when searching numbers

[rethish]

Hi All,

Please help with this issue. Been bogged down all morning.

I have to search a value in a list of values and give the correspondin
value in the same row in a different cell.

In my Column B, I have 309.5,328.8,-410.5,-80.3,-1.4

In Column A, I have 1228.3,943.7,44.4,301.1,688.7.

The formula needs to search for 410.5 in Column B and subsequently giv
44.4 as result.

I tried this formula but doesnt work at all.


=INDEX(A1:B5,MATCH("*"&D1&"*",B1:B5,0),1)

Where D1 is the cell where 410.5 resides and needs to be looked i
Column B.


Thanks alot.

 

[Claus Busch]

Hi,

Am Mon, 2 Apr 2012 02:52:14 +0000 schrieb rethish:

In my Column B, I have 309.5,328.8,-410.5,-80.3,-1.4

In Column A, I have 1228.3,943.7,44.4,301.1,688.7.

The formula needs to search for 410.5 in Column B and subsequently give
44.4 as result.

try:
=INDEX(A1:A5,MATCH(D1,ABS(B1:B5),1))
This formula is an array formula to enter with CTRL+SHIFT+ENTER


Regards
Claus Busch