PDA

View Full Version : match data in certain ranges



hunna
11-18-2011, 10:56 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".

=(INDEX($S$12:$S$3054,MATCH(1,INDEX(($S$12:$S$3054>=W17-$AP$35)*($S$12:$S$3054<=W17+$AP$35),0),0)))

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

=(INDEX($S$12:$S$3054, $T$12:$T$3054,MATCH(1,INDEX(($S$12:$S$3054, $T$12:$T$3054>=W17-$AP$35)*($S$12:$S$3054, $T$12:$T$3054<=W17+$AP$35),0),0)))

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.

mgm05267
11-18-2011, 11:14 AM
Hi Hunna,

Can you post a sample file....

Regards,

MGM

hunna
11-19-2011, 01:23 AM
Hello mgm05267 (http://www.vbaexpress.com/forum/member.php?u=42351),

I already posted the file. Hope it's clear.

Thank for your help

nilem
11-19-2011, 02:31 AM
may be something like this:

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

hunna
11-19-2011, 04:43 AM
Hi nilem,
Thank you for your answer

I am sorry for unclear explanation.

From your code, data in column A match to column C and E, if both cases are true then column A turns yellow.

The one I am looking for is data in column A match to column C and E. If column A match to column C or column A match to column E or both are true then column A turns yellow

A --> C ---> true --> yellow
A --> E --> true --> yellow
A --> C and E --> true --> yellow