match data in certain ranges

**copythat** · 11-18-2011, 10:50 AM

Hi all, I have a fomular that matches the data in certain range (in the code is "W17") to another (in the code is $S$12:$S$3054 ) with some error.
The error is determined in "$AP$35".

Please Login or Register  to view this content.

My problem is I want to match the data that is not in the same area (I am not sure how to call it), for example

Please Login or Register  to view this content.

In the example I want to match "W17" (with the error in $AP$35) to $S$12:$S$3054 and $T$12:$T$3054

but the code is not working, so can anybody help me for this?

Any help would be appreciated.

**pike** · 11-19-2011, 02:30 AM

Hi copythat
a sample workbook would be handy

**copythat** · 11-19-2011, 07:03 AM

Hello Pike,

I already posted the example file and hope it's clear.

Thanks,

**pike** · 11-20-2011, 05:47 AM

Hi copythat

An If function can be used to swtich columns if these is no match

=IF(ISNUMBER(INDEX($A$2:$A$6,MATCH(1,INDEX(($C$2:$C$6>=A2-$H$2)*($C$2:$C$6<=A2+$H$2),0),0))),INDEX($A$2:$A$6,MATCH(1,INDEX(($C$2:$C$6>=A2-$H$2)*($C$2:$C$6<=A2+$H$2),0),0)),INDEX($A$2:$A$6,MATCH(1,INDEX(($E$2:$E$6>=A2-$H$2)*($E$2:$E$6<=A2+$H$2),0),0)))