×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Combining If and Iferror and Index/Match with Hyperlink

Combining If and Iferror and Index/Match with Hyperlink

Combining If and Iferror and Index/Match with Hyperlink

(OP)

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

RE: Combining If and Iferror and Index/Match with Hyperlink

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

combo

RE: Combining If and Iferror and Index/Match with Hyperlink

(OP)
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

RE: Combining If and Iferror and Index/Match with Hyperlink

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 tWikiTree[WikiTree Link] = "".

combo

RE: Combining If and Iferror and Index/Match with Hyperlink

(OP)
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

RE: Combining If and Iferror and Index/Match with Hyperlink

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)), ""),"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

RE: Combining If and Iferror and Index/Match with Hyperlink

(OP)
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?

RE: Combining If and Iferror and Index/Match with Hyperlink

For me the second argument in OR function (what Andy marked green) should be:
INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))=""
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.

combo

RE: Combining If and Iferror and Index/Match with Hyperlink

(OP)
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

RE: Combining If and Iferror and Index/Match with Hyperlink

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Combining If and Iferror and Index/Match with Hyperlink

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

RE: Combining If and Iferror and Index/Match with Hyperlink

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: ISNA(MATCH($E$2, tWikiTree[Master ID],0))
Returns TRUE if no match in tWikiTree, otherwise FALSE,
arg. 2: INDEX(tWikiTree[WikiTree Link],MATCH($E$2, tWikiTree[Master ID],0))=""
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

RE: Combining If and Iferror and Index/Match with Hyperlink

(OP)
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

RE: Combining If and Iferror and Index/Match with Hyperlink

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

RE: Combining If and Iferror and Index/Match with Hyperlink

(OP)
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?

RE: Combining If and Iferror and Index/Match with Hyperlink

Plz post the formula in question

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Combining If and Iferror and Index/Match with Hyperlink

(OP)
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))))

RE: Combining If and Iferror and Index/Match with Hyperlink

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:
=ISNA(MATCH($E$2, tArtwork[Master-ID],0))
If not found, formula returns TRUE, otherwise FALSE, as expected.
(2). missing Master-ID or empty entry:
=IFERROR(INDEX(tArtwork[Description],MATCH($E$2,tArtwork[Master-ID],0))="",TRUE)
If Master-ID exists and Description is not empty, returns FALSE, otherwise TRUE.

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

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:
=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))))

combo

RE: Combining If and Iferror and Index/Match with Hyperlink

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close