0

I have a list of for example 25 cryptos that I invest in. In a separate sheet called "CryptoJayTargets", I have his price estimate for year end for a list of 15 cryptos.

I want to create a formula to lookup his target, but if his target isn't found (i.e. I don't have the crypto symbol on that sheet, I want to set the value to 3x my purchase price from Column R5). [Column P5 is the symbol/abbreviation for the crypto].

The VLookup works fine when there is a match on the symbol/abbreviation. But when no match found it just says "#N/A" in the column.

Here are three things I tried. Seems like I want an "else" on the IFERROR function.

=IF(VLOOKUP($P5,CryptoJayTargets!$A$1:$C$100,3,FALSE)='#N/A',3*$R5,VLOOKUP($P5,CryptoJayTargets!$A$1:$C$100,3,FALSE))

This is wrong because IFERROR requires only two arguments:

=IFERROR(VLOOKUP($P5,CryptoJayTargets!$A$1:$C$100,3,FALSE),3*$R5,VLOOKUP($P5,CryptoJayTargets!$A$1:$C$100,3,FALSE))

And also:

=IF(IFERROR(VLOOKUP($P5,CryptoJayTargets!$A$1:$C$100,3,FALSE),'ERROR')='ERROR',3*$R5,VLOOKUP($P5,CryptoJayTargets!$A$1:$C$100,3,FALSE))

and:

=IF(IFERROR(VLOOKUP($P10,CryptoJayTargets!$A$1:$C$100,3,FALSE),3*$R10),VLOOKUP($P10,CryptoJayTargets!$A$1:$C$100,3,FALSE))

Improve this question

1 Answer 1

Reset to default
0

I think I got it, needed to us ISNUMBER(MATCH,Lookup)):

=IF(ISNUMBER(MATCH($P5,CryptoJayTargets!$A$1:$A$100,0)),VLOOKUP($P5,CryptoJayTargets!$A$1:$C$100,3,FALSE),$M5*3)

based on this question on StackOverflow.

To break it down, I got this working first:

=MATCH($P6,CryptoJayTargets!A1:A100,0)

which return a row number of the match, of "#N/A". I had to user parm=0 because my data was not sorted.

Would be nice to have one GoogleDoc function to do this.

Improve this answer
1
  • Congratulations on creating a working solution. However, it is overcomplicated. One shorter formula could process the entire range (rather than dragging this long formula down). However, it would not be efficient to try to write that formula without seeing the full details of your sheet. If you'd like, share a link to a copy of your sheet and clearly indicate where the results should go. Once that is available, I or others here should be able to quite easily suggest a more concise solution.
    – Erik Tyler
    Aug 28, 2021 at 23:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.