| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Checking a net drive | Excel Discussion (Misc queries) | |||
| Why does spelling check close Excel when checking spanish? | Excel Discussion (Misc queries) | |||
| error checking reports a value in the formula is of the wrong dat. | Excel Worksheet Functions | |||
Linear Mode
