I have the following 2 google sheets

TEST 1 https://docs.google.com/spreadsheets/d/1mAssNMTGXcMcuYhjDWq6lNfWOdgUfI6FbGSWushGAMg/edit?usp=sharing

TEST 2 https://docs.google.com/spreadsheets/d/15PAI8nnTzp1wQuvvkHxZ81kkeRrqHrvUH1vXiF9tya0/edit?usp=sharing

In Test 2 in cells B36,B37 and C36,C37 I am trying to grab information from TEST 1 to TEST 2

I have checked and checked and triple checked but I am not sure why it is not grabbing the information over.

Previously I had used "-" and "( )" in the table names so I decided to remove all of those and use only letters but it is still not working.

I also noticed that it was grabbing from the correct column (in my original google sheet), column D but it was grabbing the wrong information.

But I checked the name I was using to match "Room BS Harvest" and made sure it matched in both google sheets but still was not able to get it to work.

I have turned on show formula so you can see that I have formulas in Test 2 B36,B37,C36,C37

If you have any idea why it is not working please do let me know. I can't figure out why it is not getting the data over.

CodePudding user response：

I myself would not approach things the way you are doing in this sheet. (I always recommend using a separate sheet within your destination spreadsheet where IMPORTRANGE brings in all of the data from the source location, and then using that single sheet in the destination spreadsheet as the reference for all other formulas, such as the ones you're trying to use.)

In addition, your sheets are inaccessible (i.e., "Comment only"). So neither I nor anyone else would be able to setup an alternative method for you to consider.

That said, and working with what you do have, here is how you might adjust the B36 formula...

Here is the formula you originally wrote (and which is not working):

=INDEX(importrange("https://docs.google.com/spreadsheets/d/1mAssNMTGXcMcuYhjDWq6lNfWOdgUfI6FbGSWushGAMg/edit#gid=0",$J$1&"!A4:H26"),MATCH($A36,index(importrange("https://docs.google.com/spreadsheets/d/1mAssNMTGXcMcuYhjDWq6lNfWOdgUfI6FbGSWushGAMg/edit#gid=0",$J$1&"!A4:A26"))),match($J$2,index(importrange("https://docs.google.com/spreadsheets/d/1mAssNMTGXcMcuYhjDWq6lNfWOdgUfI6FbGSWushGAMg/edit#gid=0",$J$1&"!A4:H4"))))

Here is my edit to that formula structure (which should work):

=INDEX(IMPORTRANGE("1mAssNMTGXcMcuYhjDWq6lNfWOdgUfI6FbGSWushGAMg",$J$1&"!A4:H26"),MATCH($A36,IMPORTRANGE("1mAssNMTGXcMcuYhjDWq6lNfWOdgUfI6FbGSWushGAMg",$J$1&"!A4:A26"),0),MATCH($J$2,index(IMPORTRANGE("1mAssNMTGXcMcuYhjDWq6lNfWOdgUfI6FbGSWushGAMg",$J$1&"!A4:H4")),0))

Notice that you only need the spreadsheets ID number for IMPORTRANGE, not the entire URL. This just makes reading such formulas easier.

You also had mismatched sets of parentheses. This was your biggest issue.

And I recommend using the third parameter of MATCH to indicate what kind of match you're looking for. (Here, I assigned 0, which means "exact match only.")

Hopefully, you can use that as a model for editing your other formulas.