I am just so weak; I could not let this go. I used =NA() to get #NA and
replicated the formula as follows (spaces inserted):
=IF(ISNA(VLOOKUP("calendar";MyLookup;2;0));
IF(ISNA(VLOOKUP("google";MyLookup;2;0));"";
VLOOKUP("calendar";MyLookup;2;0);VLOOKUP("google";MyLookup;2;0))
The result I get is ERR:504.
Terry wrote:
I have inserted comments
Walter A. March wrote:
Terry wrote:
Does your hyperlink include a text label? A8 should contain the
equivalent of =HYPERLINK("some jpeg";1216) or, if 1216 is a label,
"1216".
If you are using the hyperlink dialog, make sure that only 1216 is in
for Text. watch out for spaces or apostrophes (').
I'm surprised that I can't replicate your problem. See, what I think
is happening is that the hyperlink makes the number into text and
vlookup, as far as I know, doesn't match text to a number.
VLOOKUP returns whatever is in the cell looked up. If it's necessary to
convert text to a number, James could use VALUE(VLOOKUP(...))
I think there's something wrong with your formula as posted (probably
double pasting):
=IF(ISNA(VLOOKUP(A8; Jobs.$J$4:$P$1010; 7; 0)); IF(ISNA(VLOOKUP(A8;
Ravy.$J$4:$P$1010; 7; 0)); ""; VLOOKUP(A8; Ravy.$J$4:$P$1010; 7; 0));
VLOOKUP(A8; Jobs.$J$4:$P$1010; 7; 0))
should be :
=IF(ISNA(VLOOKUP(A8; Jobs.$J$4:$P$1010; 7; 0)); ""; VLOOKUP(A8;
Jobs.$J$4:$P$1010; 7; 0))
Depends on what you are trying to accomplish.
The first formula says if it is not on the Jobs tab, look on the Ravy
tab. If it is not there either, return "" otherwise give the match
from Ravy. If it is on the Jobs tab, give the match from there.
The second one just returns what is on Jobs.
I was going by what James said:
"So, if looking up A8 (let's say "1216") gives us "N/A", then print null
(blank field);
If A8="1216" is found then look up the corresponding job number."
If the intention is as you say, the formula still seems to follow the
wrong sequence, i.e. condition, result one, result two. I'm surprised
that the formula does not result in Error:504.
I would have thought two separate formulas were required, the second
applying only if the first yielded an empty string. Something like
=IF(AND(<formula one cell>="";ISNA(<etc>));"";VLOOKUP(<second lookup>))
It would take too much work to set up a test so I'll go along with the
consensus.
I don't even know how to get #NA from VLOOKUP.
--
If you're seeking, check out http://www.rci.org.au
Send instant messages to your online friends http://au.messenger.yahoo.com
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
