Terry wrote:
Walter A. March wrote:
Terry wrote:
<snip> The result I get is ERR:504.
Try
=IF(ISNA(VLOOKUP("calendar";MyLookup;2;0));
IF(ISNA(VLOOKUP("google";MyLookup;2;0));"";
VLOOKUP("google";MyLookup;2;0));VLOOKUP("calendar";MyLookup;2;0))
without the spaces.
If you don't find calendar, look for google. If you did find calendar,
skip to the end and do the last vlookup.
If you didn't find google, return "". If you did find google, return
the match.
Put another way,
Our initial if statement is
IF(ISNA(VLOOKUP("calendar";MyLookup;2;0))
when true, run this: IF(ISNA(VLOOKUP("google";MyLookup;2;0));"";
VLOOKUP("google";MyLookup;2;0))
when false, run this: VLOOKUP("calendar";MyLookup;2;0)
Thanks for that. I needed to look more closely. The second sequence is
result one of the first sequence. I had my results two back to front
and missed a parenthesis.
Other comments:
> VLOOKUP returns whatever is in the cell looked up. If it's necessary
> to convert text to a number, James could use VALUE(VLOOKUP(...))
We don't want to convert the answer to the VLOOKUP. We would want to
convert what we are looking for to a number.
Quite right. VALUE(A8)
> I don't even know how to get #NA from VLOOKUP.
That would happen if the data you are looking for, google, in your
example, is not in the data set. That is, #N/A is what VLOOKUP returns
when it doesn't find what you are trying to look up.
Thanks again. Fortuitously, I discovered that later yesterday when
experimenting with another VLOOKUP by changing the value looked up
before altering the lookup range. If I've ever looked up something not
in the list, it was a long time ago.
Glad I could help. I used to support M$ Excel very, very heavily and
VLOOKUP was a big part of that... and frequently what we were looking
for was not in the list we were looking in.
And there must be some Law about the more complicated a formula is, the
more likely you missed or misplaced a comma. :)
WalterAM
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
