|
Add checking
{=IF(ISERROR(INDEX(P05!$AO$4:$AO$1000,MATCH(CC!$C7 7,P05!$BL$24:$BL$1000,0))),"",INDEX(P05!$AO$4:$AO$ 1000,MATCH(CC!$C77,P05!$BL$4:$BL$1000,0)))}
Once the above formula matches I want then to check if the corresponding cell in P05!Y$24:Y$1000 contains a value. The value must be in the correct cell to give a true result. Can anyone help me with this? Thanks Pat |
Not sure, but you could try something like:
=IF(ISNA(MATCH(CC!$C77,'P05'!$BL$4:$BL$1000,0)),"" ,IF(INDEX('P05'!Y$4:Y$1000 ,MATCH(CC!$C77,'P05'!$BL$4:$BL$1000,0))="Test1","Y es","No")) (normal ENTER will do) where the corresponding value to be checked in the range*: 'P05'!Y$4:Y$1000 is "Test1" when the match occurs in: MATCH(CC!$C77,'P05'!$BL$4:$BL$1000,0) *corrected the range refs to read as "Y$4" (instead of "Y$24" as posted) The formula will return "Yes" if the corresponding cell in 'P05'!Y$4:Y$1000 contains: "Test1" when the match occurs If there is a match but the corresponding value is not "Test1", "No" will be returned If there isn't any match, blank: "" will be returned -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Pat" wrote in message ... {=IF(ISERROR(INDEX(P05!$AO$4:$AO$1000,MATCH(CC!$C7 7,P05!$BL$24:$BL$1000,0))) ,"",INDEX(P05!$AO$4:$AO$1000,MATCH(CC!$C77,P05!$BL $4:$BL$1000,0)))} Once the above formula matches I want then to check if the corresponding cell in P05!Y$24:Y$1000 contains a value. The value must be in the correct cell to give a true result. Can anyone help me with this? Thanks Pat |
Hi Max,
Not quite what I am looking for, the original code: {=IF(ISERROR(INDEX(P05!$AO$4:$AO$1000,MATCH(CC!$C7 7,P05!$BL$24:$BL$1000,0))),"",INDEX(P05!$AO$4:$AO$ 1000,MATCH(CC!$C77,P05!$BL$4:$BL$1000,0)))} will return a result from P05!$AO$4:$AO$1000, if a match is found. I still want whatever value is found in P05!$AO$4:$AO$1000 returned, but only after checking if P05!Y$4:Y$1000 has a value in the corresponding cell. If there is no matching cell in the range P05!Y$4:Y$1000, return nothing. "Max" wrote in message ... Not sure, but you could try something like: =IF(ISNA(MATCH(CC!$C77,'P05'!$BL$4:$BL$1000,0)),"" ,IF(INDEX('P05'!Y$4:Y$1000 ,MATCH(CC!$C77,'P05'!$BL$4:$BL$1000,0))="Test1","Y es","No")) (normal ENTER will do) where the corresponding value to be checked in the range*: 'P05'!Y$4:Y$1000 is "Test1" when the match occurs in: MATCH(CC!$C77,'P05'!$BL$4:$BL$1000,0) *corrected the range refs to read as "Y$4" (instead of "Y$24" as posted) The formula will return "Yes" if the corresponding cell in 'P05'!Y$4:Y$1000 contains: "Test1" when the match occurs If there is a match but the corresponding value is not "Test1", "No" will be returned If there isn't any match, blank: "" will be returned -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Pat" wrote in message ... {=IF(ISERROR(INDEX(P05!$AO$4:$AO$1000,MATCH(CC!$C7 7,P05!$BL$24:$BL$1000,0))) ,"",INDEX(P05!$AO$4:$AO$1000,MATCH(CC!$C77,P05!$BL $4:$BL$1000,0)))} Once the above formula matches I want then to check if the corresponding cell in P05!Y$24:Y$1000 contains a value. The value must be in the correct cell to give a true result. Can anyone help me with this? Thanks Pat |
Maybe:
=IF(ISNA(MATCH(CC!$C77,'P05'!$BL$4:$BL$1000,0)),"" ,IF(INDEX('P05'!Y$4:Y$1000 ,MATCH(CC!$C77,'P05'!$BL$4:$BL$1000,0))="Test1",IN DEX('P05'!$AO$4:$AO$1000,M ATCH(CC!$C77,'P05'!$BL$4:$BL$1000,0)),"")) (Normal ENTER) which will return value found in 'P05'!$AO$4:$AO$1000 only if the corresponding cell in 'P05'!Y$4:Y$1000 = "Test1" -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Pat" wrote in message ... Hi Max, Not quite what I am looking for, the original code: {=IF(ISERROR(INDEX(P05!$AO$4:$AO$1000,MATCH(CC!$C7 7,P05!$BL$24:$BL$1000,0))) ,"",INDEX(P05!$AO$4:$AO$1000,MATCH(CC!$C77,P05!$BL $4:$BL$1000,0)))} will return a result from P05!$AO$4:$AO$1000, if a match is found. I still want whatever value is found in P05!$AO$4:$AO$1000 returned, but only after checking if P05!Y$4:Y$1000 has a value in the corresponding cell. If there is no matching cell in the range P05!Y$4:Y$1000, return nothing. "Max" wrote in message ... Not sure, but you could try something like: =IF(ISNA(MATCH(CC!$C77,'P05'!$BL$4:$BL$1000,0)),"" ,IF(INDEX('P05'!Y$4:Y$1000 ,MATCH(CC!$C77,'P05'!$BL$4:$BL$1000,0))="Test1","Y es","No")) (normal ENTER will do) where the corresponding value to be checked in the range*: 'P05'!Y$4:Y$1000 is "Test1" when the match occurs in: MATCH(CC!$C77,'P05'!$BL$4:$BL$1000,0) *corrected the range refs to read as "Y$4" (instead of "Y$24" as posted) The formula will return "Yes" if the corresponding cell in 'P05'!Y$4:Y$1000 contains: "Test1" when the match occurs If there is a match but the corresponding value is not "Test1", "No" will be returned If there isn't any match, blank: "" will be returned -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Pat" wrote in message ... {=IF(ISERROR(INDEX(P05!$AO$4:$AO$1000,MATCH(CC!$C7 7,P05!$BL$24:$BL$1000,0))) ,"",INDEX(P05!$AO$4:$AO$1000,MATCH(CC!$C77,P05!$BL $4:$BL$1000,0)))} Once the above formula matches I want then to check if the corresponding cell in P05!Y$24:Y$1000 contains a value. The value must be in the correct cell to give a true result. Can anyone help me with this? Thanks Pat |
I'm impressed, well done Max!
Cheers mate Pat "Max" wrote in message ... Maybe: =IF(ISNA(MATCH(CC!$C77,'P05'!$BL$4:$BL$1000,0)),"" ,IF(INDEX('P05'!Y$4:Y$1000 ,MATCH(CC!$C77,'P05'!$BL$4:$BL$1000,0))="Test1",IN DEX('P05'!$AO$4:$AO$1000,M ATCH(CC!$C77,'P05'!$BL$4:$BL$1000,0)),"")) (Normal ENTER) which will return value found in 'P05'!$AO$4:$AO$1000 only if the corresponding cell in 'P05'!Y$4:Y$1000 = "Test1" -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Pat" wrote in message ... Hi Max, Not quite what I am looking for, the original code: {=IF(ISERROR(INDEX(P05!$AO$4:$AO$1000,MATCH(CC!$C7 7,P05!$BL$24:$BL$1000,0))) ,"",INDEX(P05!$AO$4:$AO$1000,MATCH(CC!$C77,P05!$BL $4:$BL$1000,0)))} will return a result from P05!$AO$4:$AO$1000, if a match is found. I still want whatever value is found in P05!$AO$4:$AO$1000 returned, but only after checking if P05!Y$4:Y$1000 has a value in the corresponding cell. If there is no matching cell in the range P05!Y$4:Y$1000, return nothing. "Max" wrote in message ... Not sure, but you could try something like: =IF(ISNA(MATCH(CC!$C77,'P05'!$BL$4:$BL$1000,0)),"" ,IF(INDEX('P05'!Y$4:Y$1000 ,MATCH(CC!$C77,'P05'!$BL$4:$BL$1000,0))="Test1","Y es","No")) (normal ENTER will do) where the corresponding value to be checked in the range*: 'P05'!Y$4:Y$1000 is "Test1" when the match occurs in: MATCH(CC!$C77,'P05'!$BL$4:$BL$1000,0) *corrected the range refs to read as "Y$4" (instead of "Y$24" as posted) The formula will return "Yes" if the corresponding cell in 'P05'!Y$4:Y$1000 contains: "Test1" when the match occurs If there is a match but the corresponding value is not "Test1", "No" will be returned If there isn't any match, blank: "" will be returned -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Pat" wrote in message ... {=IF(ISERROR(INDEX(P05!$AO$4:$AO$1000,MATCH(CC!$C7 7,P05!$BL$24:$BL$1000,0))) ,"",INDEX(P05!$AO$4:$AO$1000,MATCH(CC!$C77,P05!$BL $4:$BL$1000,0)))} Once the above formula matches I want then to check if the corresponding cell in P05!Y$24:Y$1000 contains a value. The value must be in the correct cell to give a true result. Can anyone help me with this? Thanks Pat |
You're welcome, Pat
Thanks for feedback ! -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Pat" wrote in message ... I'm impressed, well done Max! Cheers mate Pat |
| All times are GMT +1. The time now is 09:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com