CODEWITHSUNDEEP
google-sheets
dj17
dj17

Reputation: 29

HLOOKUP evaluates to an out of bounds range error in Google Sheets

I'm trying HLOOKUP with Importrange formula as I've to lookup data from other Google Sheets.

I'm entering the below formula.

=HLOOKUP(C1,IMPORTRANGE("1wWguGb6O0GyX7ACxzoFCN8N73zV0pUeoj51R_zFNPfE","Project wise Resources!$E$1:$AZ$20"),2,0)

I'm getting the error:

HLOOKUP evaluates to an out of bounds range.

I have entered the correct range but unable to understand what the issue is.

Upvotes: 3

Views: 8813

Answers (2)

player0
player0

Reputation: 1

  • you can't do that on the spot
  • first paste this formula into some cell and allow access
=IMPORTRANGE("1wWguGb6O0GyX7ACxzoFCN8N73zV0pUeoj51R_zFNPfE",
             "'Project wise Resources'!E1:AZ20")

  • then use your formula: 
=HLOOKUP(C1, IMPORTRANGE("1wWguGb6O0GyX7ACxzoFCN8N73zV0pUeoj51R_zFNPfE",
                         "'Project wise Resources'!E1:AZ20"), 2, 0)

  • and also make sure that sheet Project wise Resources has a column AZ

Upvotes: 1

JGFMK
JGFMK

Reputation: 8904

Try changing last HLOOKUP option to TRUE/FALSE instead of 0

HLOOKUP: https://support.google.com/docs/answer/3093375?hl=en

Horizontal lookup. Searches across the first row of a range for a key and returns the value of a specified cell in the column found.

VLOOKUP: https://support.google.com/docs/answer/3093318?hl=en&ref_topic=3105472

Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified cell in the row found.

IMPORTRANGE: https://support.google.com/docs/answer/3093340?hl=en

you may need VLOOKUP depending on way your lookup is organised - by rows or columns..

If you are lookup up second row or second column only, then there is no need to have a range that extends beyond 2 rows or 2 columns...

Upvotes: 0

Related Questions