# Solved Google Docs - Sum data from multiple Sheets

July 13, 2010 at 03:49:45
Specs: Windows 7

See More: Google Docs - Sum data from multiple Sheets

August 24, 2010 at 07:18:01

 Hi,As you have the data in the order Name ... Nickname, VLOOKUP will not work for returning a Name for a given Nickname.VLOOKUP always looks for a match in the first column of the lookup range and returns a result from a column to the right.You could try a combination of Match and Offset=OFFSET(Sheet1!\$C\$1,MATCH(B7,Sheet1!\$C\$2:\$C\$61,0),-1)Match returns the number of rows down the list that it finds a match, and then this number is used as a row offset, for the Offset function, and -1 is the column Offset.If there is no match, then you get an #NA error, so use this:=IF(ISNA(MATCH(B7,Sheet1!\$C\$2:\$C\$61,0)),"No match",OFFSET(Sheet1!\$C\$1,MATCH(B7,Sheet1!\$C\$2:\$C\$61,0),-1))Regards

#1
July 15, 2010 at 06:08:27

Report •

#2
July 16, 2010 at 01:17:14

Report •

#3
August 24, 2010 at 04:21:28

 The previous response (from Humar) worked a treat, albeit with a few minor modifications that they wouldn't have know about...I have stumbled on a slightly different (one-off) issue now, where I need to be able to to get the user's name from their nickname, but I can't get the VLOOKUP to work properly. I can get it to bring up their username if I put their username (not very useful) and I can get their nickname if I put in their username, but I'm trying to use get the username from the nickname.....and that's in both GoogleDocs and Excel..Columns are as follows, just to save confusion:Monthly sheet, where the formula will be:Name | NicknameMain sheet, where the master list of users is:[blank] | Name | NicknameSo far, the closest I have been able to get, in my Excel test spreadie is:=VLOOKUP(B7,Sheet1!\$B\$2:\$D\$61,1)[[B7 = random user's name, result: same user's name - in this example, that is User7]]or:=VLOOKUP(B9,Sheet1!\$B\$2:\$D\$61,2)[[B9 = random user's name, result: same user's nickname - in this example, that is also User7]]Unfortunately those don't work, but it is at least a little more useful than the result of either of the following formulae - which just bring up "#N/A":=VLOOKUP(B6,Sheet1!\$B\$2:\$D\$61,1)[[B6 = random user's nickname - in this example, that is Nick3]]or:=VLOOKUP(B8,Sheet1!\$B\$2:\$D\$61,2)[[B8 = random user's nickname - in this example, that is Nick3]]I did try to make the search area wider, to try to get it to pick up the correct column, but that just seems to have thrown an error straight off the bat..From this VLOOKUP example page ( http://office.microsoft.com/en-us/e... ) I'm trying to get the formula in Column A to pick up the adjacent cell's entry (Column B) and find it in the other sheet, then give me the correlating Username for that....but I can't manage to tweak the formula/e to get it to work..Anyone here have any suggestions..?

Report •

Related Solutions

#4
August 24, 2010 at 07:18:01

 Hi,As you have the data in the order Name ... Nickname, VLOOKUP will not work for returning a Name for a given Nickname.VLOOKUP always looks for a match in the first column of the lookup range and returns a result from a column to the right.You could try a combination of Match and Offset=OFFSET(Sheet1!\$C\$1,MATCH(B7,Sheet1!\$C\$2:\$C\$61,0),-1)Match returns the number of rows down the list that it finds a match, and then this number is used as a row offset, for the Offset function, and -1 is the column Offset.If there is no match, then you get an #NA error, so use this:=IF(ISNA(MATCH(B7,Sheet1!\$C\$2:\$C\$61,0)),"No match",OFFSET(Sheet1!\$C\$1,MATCH(B7,Sheet1!\$C\$2:\$C\$61,0),-1))Regards

Report •

#5
August 24, 2010 at 07:33:27

 BRILLIANT..!!With a bit of minor tweaking, to suit the actual columns in the GDocs spreadie, it worked perfectly..!Here is the final version:=OFFSET('Master Sheet'!\$C\$1,MATCH(B2,'Master Sheet'!\$C\$2:\$C\$582,0),-1)Thank you so much.!

Report •