# Searching cells and pulling values in Google Sheets?

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:

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)`

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.

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

• 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, 2021 at 14:01
• Glad I could help. Thank you for taking the time to accept. Feb 13, 2021 at 18:16