2

sorry for the vague title but I'm not sure how exactly to describe this.

I've created a spreadsheet and used the Coinmarketcap API to pull lots of data, here's the relevant part:

enter image description here

From another sheet I want to be able to search this pull values based on the 2nd column across (J). So for example, if I have a value of 'BTC' in my sheet I want it to search through this list and pull out the values from column AG in that row.

I've tried a VLOOKUP like so:

=VLOOKUP(B10,CMC!I2:AM101, 25)

With B10 being the value 'BTC' but it only ever gets the value from the 'Binance Coin' (BNB) row. 25 is correctly pulling the 'AG' column at least. I've just tested manually writing "BTC" or "DOT" into the start of the VLOOKUP formula and I'm having the same problem:=VLOOKUP("DOT",CMC!I2:AM101,25)

1

For your formula to work you should always remember the following 3:

About VLOOKUP

  1. VLOOKUP only calculates from left to right.
  2. The first column in the range is searched for the key specified in 'search_key'.
  3. Gives different results based on whether the column to be searched (the first column of the specified range) is sorted or not.

So your formula would be

=VLOOKUP(B10,{CMC!J2:J101,CMC!I2:I101,CMC!K2:AG101},25,FALSE)

Using the curly brackets { } we create a virtual array, thus making column J the first column in our range.

Pro Tip
We can take the formula a step further and omit all the in-between, un-wanted columns.
Now our formula becomes

=VLOOKUP(B2,{CMC!J2:J,CMC!I2:I,CMC!AG2:AG},3,FALSE)

OR

=VLOOKUP("DOT",{CMC!J2:J,CMC!I2:I,CMC!AG2:AG},3,FALSE)

enter image description here

2
  • 1
    Thank you SO much, I cannot even put into words how grateful I am for you not only providing an answer but also explaining what I was doing wrong and how VLOOKUP works. Really appreciate it!
    – Nick
    Feb 13 at 14:01
  • 1
    Glad I could help. Thank you for taking the time to accept. Feb 13 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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