Combining If and Iferror and Index/Match with Hyperlink

[cneill]

Some help would be greatly appreciated
I am struggling to find a way to combine these two functions
The aim if to returns one of the following three results
The selected "Master ID" is in Cell $E$2
The Function is in Cell F2
1.) The "Master ID" that is selected from cell $E$2 finds a matching "Master ID" in the table tWikiTree and returns the "WikiTree Link" (The WikiTree URL) as a Hyperlink
2.) If there is no Matching "Master ID" the result is "No Current Link to WikiTree"
3.) If there is a Match but there is no URL in the Cell, the result is "No Current Link to WikiTree"

The following function
=IF(INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))="","No Current Link to WikiTree", HYPERLINK(INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))))
Returns the correct results as in 1 and 3 but for 2 returns #N/A

The following function
=IFERROR(INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0)),"No Current Link to WikiTree")
Returns the correct results as in 1 and 2 but for 3 returns 0

I am not sure but the problem appear to be caused when I add the HYPERLINK to the IfError function, hence no HYPERLINK in the second function
Any suggestions on how to combine them would be great

 

[combo]

You can have scenarios (2) and (3) that require to handle, so both conditions have to be checked.
Pseudoformula:
[tt]=IF(OR(ISNA([test MasterID match in tWikiTree]),[link = ""]),"No Current Link to WikiTree",HYPERLINK( ... ))[/tt]



combo

 

[cneill]

Hi Combo,

Thank you
I have tried to rework the function as you describe but I must have something wrong
New Function
=IF(OR(ISNA(MATCH($E$2, tWikiTree[Master ID],0)),tWikiTree[WikiTree Link] = ""),"No Current Link to WikiTree", HYPERLINK(INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))))

I just get "No Current Link to WikiTree" for every scenario

Can you see where I have gone wrong?
Thanks

 

[combo]

In 'OR' you need two conditions. The first seems to be ok, in the second the found value has to be checked for ="". You put [tt]tWikiTree[WikiTree Link] = ""[/tt].

combo

 

[cneill]

Hi Combo,

Fixed
So we now have =IF(OR(ISNA(MATCH($E$2, tWikiTree[Master ID],0)), ""),"No Current Link to WikiTree", HYPERLINK(INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))))

This works fine
but now point 2 returns "blank" instead of #N/A which is great
Is there a way to modify the function further, so that when it returns "blank" it also says "No Current Link to WikiTree"

Thanks

 

[Andrzejek]

I would assume it does not return "blank", but it returns "" (blank) - huge difference.
And since the only "" (blank) is here:

=IF(OR(ISNA(MATCH($E$2, tWikiTree[Master ID],0)), [highlight #8AE234]""[/highlight]),"No Current Link to WikiTree", HYPERLINK(INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))))

I would guess that's where you want to put whatever you want to say.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[cneill]

Hi Andrzejek,

Yes your right, I tried that but it just returns (blank)
If I manually add "No Current Link to WikiTree" to a (blank) cell on the tWikiTree, then it will return "No Current Link to WikiTree"

Any Thoughts?

 

[combo]

For me the second argument in OR function (what Andy marked green) should be:
[tt]INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))=""[/tt]
As I marked above, you should test here, if found value is blank. If one of conditions is not satisfied, there is no proper link, otherwise you can return the link.[tt][/tt]

combo

 

[cneill]

Hi Combo
Adjusted the function to this
=IF(OR(ISNA(INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))="")),"No Current Link to WikiTree", HYPERLINK(INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))))

But for some reason it is still not quite right
Let me give you what is actually happening with this Function

I am testing three Master ID's
Master ID "1" there is no Index/Match as there is no Master ID "1" in Table tWikiTree
The Value of this part of the Function INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))="" = #N/A
Value if True = "No Current Link to WikiTree"
Value if False = HYPERLINK(INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))))
So it Returns "No Current Link to WikiTree" which is correct

Master ID "18" there is an Index/Match as there is a Master ID of "18" in Table tWikiTree and there is a URL in the cell (tWikiTree[WikiTree Link]
The Value of this part of the Function INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))="" = False
Value if True = "No Current Link to WikiTree"
Value if False = HYPERLINK(INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))))
So it Returns "the URL" which is correct

Master ID "31" there is an Index/Match as there is a Master ID of "31" in Table tWikiTree but there is no URL in the cell (tWikiTree[WikiTree Link] as it is (Empty)
The Value of this part of the Function INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))="" = True as there is an Index/Match
Value if True = "No Current Link to WikiTree"
Value if False = ""
So it Returns (Nothing) which is not correct
If I change ="" to = "No Current Link to WikiTree"
It still says the Value if False = ""

I hope this helps as this is driving me mad

 

[SkipVought]

Why are you using non-existent IDs?

You can select from a Unique list of tWikiTree[Master ID] to feed a Data > Validation > List.

Use the tip from combo that I saw in one of your posts where he suggested using the INDIRECT() function when referencing structured tables in Data Validation List in-cell drop downs.

I this way you will only be selecting valid IDs for your formulas.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[cneill]

Thanks Skip
I have now finally sorted it using a Nested If

 

[combo]

You do not implement what I suggested.

OR function should have TRUE/FALSE as arguments. In proposed solution they should correspond to conditions (1) and (2) from your post, where there is no proper hyperlink.

So, for OR function:
arg. 1: [tt]ISNA(MATCH($E$2, tWikiTree[Master ID],0))[/tt]
Returns TRUE if no match in tWikiTree, otherwise FALSE,
arg. 2: [tt]INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))=""[/tt]
Again, returns TRUE if no text in found Master ID row.

Combining, if any of conditions is true, a message is returned, otherwise (both FALSE, that is condition (3)) - hyperlink.

Your formula has in practice only OR function with one argument.

combo

 

[cneill]

Hi Combo,
Thank you for your feed back, I am really trying to understand you and use your advice
I think this is what the function should look like, if not please explain where I am going wrong.

=IF(OR(ISNA(MATCH($E$2, tWikiTree[Master ID],0)),INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))=""),"No Current Link to WikiTree", HYPERLINK(INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))))

If I enter Master ID 1 for conditions (1) of my previous post, The first arg. ISNA(MATCH($E$2, tWikiTree[Master ID],0)) returns True and the The second arg. INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))="") Returns #N/A
Note: I have to put a ) at the end of the second arg. if not the function will not work at all

conditions (2) & conditions (3)of my previous post return the correct results
I just can't get the first condition to work.

Thanks for all your help

 

[combo]

I have no excel for this moment, will chech it later.
Your function seems to be ok.

The problem seems to be with second argument if no match in Master ID, and IF condition with OR(TRUE, #N/A). Should be TRUE instead.
You can add IFERROR function, the complete OR function:

OR(ISNA(MATCH($E$2, tWikiTree[Master ID],0)),IFERROR(INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))=""),TRUE)

combo

 

[cneill]

Hi Combo,

Thanks
I had tried that option, it just gives me a message of "You've Entered two few arguments for this function" when I press okay it highlights ""
When I check through the Function for the OR(Logical1, Logical2,Logical3 it is now telling me the new IFERROR part we have just added ,TRUE) is now the 3 argument it is not part of Argument 2
so something is still not right, any Ideas?

 

[SkipVought]

Plz post the formula in question

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[cneill]

Hi Skip,

This is the Function, which is partly working as described in previous posts

=IF(OR(ISNA(MATCH($E$2, tWikiTree[Master ID],0)),INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))=""),"No Current Link to WikiTree", HYPERLINK(INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))))

 

[combo]

I strongly recommend to debug pieces of formula in separate cells and build the whole structure next.

I checked the formula basing on your old file, so you have to change tables and columns according to your needs.
As I marked, the condition in IF formula is critical, the rest is simple: if TRUE then message about missing link, otherwise the link. It seems that the rest works.

Let's debug OR arguments:
(1). missing Master-ID:
[tt] =ISNA(MATCH($E$2, tArtwork[Master-ID],0))[/tt]
If not found, formula returns TRUE, otherwise FALSE, as expected.
(2). missing Master-ID or empty entry:
[tt]=IFERROR(INDEX(tArtwork[Description],MATCH($E$2,tArtwork[Master-ID],0))="",TRUE)[/tt]
If Master-ID exists and Description is not empty, returns FALSE, otherwise TRUE.

A combination of conditions in OR:
[tt]=OR(ISNA(MATCH($E$2, tArtwork[Master-ID],0)),IFERROR(INDEX(tArtwork[Description],MATCH($E$2,tArtwork[Master-ID],0))="",TRUE))[/tt]

It can be pasted as a condition to IF formula. But before, if one analyse the behaviour of conditions (1) and (2) above, it is easy to notice, that if (1) is TRUE, (2) is also TRUE. So only (2) can be used to switch IF selections. So, without veryfying HYPERLINK syntax:
[tt]=IF(IFERROR(INDEX(tArtwork[Description],MATCH($E$2,tArtwork[Master-ID],0))="",TRUE),"No Current Link to WikiTree", HYPERLINK(INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))))[/tt]

combo

 

[cneill]

Hi Combo,

Fixed it, thank you you so much for all your time, patience and understanding
found the error
I had =""),TRUE) should be ="",TRUE)) so the TRUE part was outside the second argument so had to include it as part of the second argument.