*Author

Offline SevsTopic starter

  • Legendary Member
  • ******
  • Posts: 2007
  • Country: us
  • Reputation Power: 26
  • Sevs is a proud Wyrm taking wing for the first time.Sevs is a proud Wyrm taking wing for the first time.Sevs is a proud Wyrm taking wing for the first time.Sevs is a proud Wyrm taking wing for the first time.Sevs is a proud Wyrm taking wing for the first time.
  • My favorite element is Oxygen
  • Awards: Slice of Elements 3rd Birthday CakeWeekly Tournament WinnerWeekly Tournament Winner
Google Docs Help https://elementscommunity.org/forum/index.php?topic=34376.msg433799#msg433799
« on: December 02, 2011, 08:01:57 am »
Since I know this community is so intelligent when it comes to Google Docs, and I have a question,

So I have a data search dilemna.

So my data goes like this

NameDateImportant #
Santa Claus12/1/20115
Ghandi12/1/20113.2
Gary Coleman12/1/20116
Santa Claus12/2/20117
So I want to find the important number using the name and the date.

I believe this can be done using the index and match function and the closest ive gotten is Example 4 here. (http://www.contextures.com/xlfunctions03.html)

But the code doesn't seem to work. Am I doing something wrong? or should I try something else entirely
"Elements is the greatest game ever made" - Abraham Lincoln

suxerz

  • Guest
Re: Excel Help https://elementscommunity.org/forum/index.php?topic=34376.msg433811#msg433811
« Reply #1 on: December 02, 2011, 09:06:08 am »
Hmm.. If I understand this correctly, from two inputs (name AND date), you want to produce one result (Important #)?
I'm not really expert in excel, but I'm pretty sure the index and match only works for 1 input - 1 output only.

On the other hand, a not very efficient formula can also be used by comparing one column at a time and using the IF function. It will look something like this:
Code: [Select]
=IF((INDEX(C2:C5,MATCH(B8,A2:A5,0)))=(INDEX(C2:C5,MATCH(B9,B2:B5,0))),INDEX(C2:C5,MATCH(B8,A2:A5,0)),"")Where
A2:A5 is the "Name" column
B2:B5 is the "Date" column
C2:C5 is the "Important #" column
B8 is where you input the Name
B9 is where you input the Date

You can also use VLOOKUP which is a tiny bit shorter and probably easier to understand.
Code: [Select]
=IF((VLOOKUP(B8,A2:C5,3,FALSE))=(VLOOKUP(B9,B2:C5,2,FALSE)),VLOOKUP(B8,A2:C5,3,FALSE),"")Not sure if this is helpful or workable; you might also want to read about the FILTER function in Googledoc. Too bad excel doesn't have this function.

Offline SevsTopic starter

  • Legendary Member
  • ******
  • Posts: 2007
  • Country: us
  • Reputation Power: 26
  • Sevs is a proud Wyrm taking wing for the first time.Sevs is a proud Wyrm taking wing for the first time.Sevs is a proud Wyrm taking wing for the first time.Sevs is a proud Wyrm taking wing for the first time.Sevs is a proud Wyrm taking wing for the first time.
  • My favorite element is Oxygen
  • Awards: Slice of Elements 3rd Birthday CakeWeekly Tournament WinnerWeekly Tournament Winner
Re: Google Docs Help https://elementscommunity.org/forum/index.php?topic=34376.msg434013#msg434013
« Reply #2 on: December 02, 2011, 09:12:21 pm »
Hmm.. If I understand this correctly, from two inputs (name AND date), you want to produce one result (Important #)?
I'm not really expert in excel, but I'm pretty sure the index and match only works for 1 input - 1 output only.

On the other hand, a not very efficient formula can also be used by comparing one column at a time and using the IF function. It will look something like this:
Code: [Select]
=IF((INDEX(C2:C5,MATCH(B8,A2:A5,0)))=(INDEX(C2:C5,MATCH(B9,B2:B5,0))),INDEX(C2:C5,MATCH(B8,A2:A5,0)),"")Where
A2:A5 is the "Name" column
B2:B5 is the "Date" column
C2:C5 is the "Important #" column
B8 is where you input the Name
B9 is where you input the Date

You can also use VLOOKUP which is a tiny bit shorter and probably easier to understand.
Code: [Select]
=IF((VLOOKUP(B8,A2:C5,3,FALSE))=(VLOOKUP(B9,B2:C5,2,FALSE)),VLOOKUP(B8,A2:C5,3,FALSE),"")Not sure if this is helpful or workable; you might also want to read about the FILTER function in Googledoc. Too bad excel doesn't have this function.
mmmm I was trying to use an arrayformula with index and match to come up with 2 inputs. the index match and vlookup formulas I think would not work because Lets say you are looking for the second Santa entry, the first will come up with the index of 1 and the second part of the if statement will come up with 4 which wont match and wont give an answer.

Ill look into the filter thing. I am not too familiar with it. Thanks for the help though
"Elements is the greatest game ever made" - Abraham Lincoln

Offline UTAlan

  • Hero Member
  • *****
  • Posts: 1802
  • Reputation Power: 58
  • UTAlan is truly a Titan, worthy of respect and acknowledgement.UTAlan is truly a Titan, worthy of respect and acknowledgement.UTAlan is truly a Titan, worthy of respect and acknowledgement.UTAlan is truly a Titan, worthy of respect and acknowledgement.UTAlan is truly a Titan, worthy of respect and acknowledgement.UTAlan is truly a Titan, worthy of respect and acknowledgement.UTAlan is truly a Titan, worthy of respect and acknowledgement.UTAlan is truly a Titan, worthy of respect and acknowledgement.UTAlan is truly a Titan, worthy of respect and acknowledgement.UTAlan is truly a Titan, worthy of respect and acknowledgement.UTAlan is truly a Titan, worthy of respect and acknowledgement.
  • Immortally Aether
  • Awards: Slice of Elements 9th Birthday CakeSlice of Elements 8th Birthday CakeSlice of Elements 7th Birthday CakeWeekly Tournament WinnerSlice of Elements 6th Birthday CakeReviver of the WikiWar #6 Winner - Team AetherSlice of Elements 3rd Birthday CakeSecond Budosei of BudokanSlice of Elements 2nd Birthday CakeWeekly Tournament Winner
Re: Excel Help https://elementscommunity.org/forum/index.php?topic=34376.msg434024#msg434024
« Reply #3 on: December 02, 2011, 09:35:40 pm »
Where
A2:A5 is the "Name" column
B2:B5 is the "Date" column
C2:C5 is the "Important #" column
B8 is where you input the Name
B9 is where you input the Date
Try this:

Code: [Select]
=INDEX(A2:C5,MATCH(TRUE,IF(B2:B5=B9,A2:A5=B8),0),3)

Offline SevsTopic starter

  • Legendary Member
  • ******
  • Posts: 2007
  • Country: us
  • Reputation Power: 26
  • Sevs is a proud Wyrm taking wing for the first time.Sevs is a proud Wyrm taking wing for the first time.Sevs is a proud Wyrm taking wing for the first time.Sevs is a proud Wyrm taking wing for the first time.Sevs is a proud Wyrm taking wing for the first time.
  • My favorite element is Oxygen
  • Awards: Slice of Elements 3rd Birthday CakeWeekly Tournament WinnerWeekly Tournament Winner
Re: Excel Help https://elementscommunity.org/forum/index.php?topic=34376.msg434095#msg434095
« Reply #4 on: December 02, 2011, 11:46:56 pm »
Where
A2:A5 is the "Name" column
B2:B5 is the "Date" column
C2:C5 is the "Important #" column
B8 is where you input the Name
B9 is where you input the Date
Try this:

Code: [Select]
=INDEX(A2:C5,MATCH(TRUE,IF(B2:B5=B9,A2:A5=B8),0),3)
Perfect Thanks
"Elements is the greatest game ever made" - Abraham Lincoln

mrgoodbar64

  • Guest
Re: Google Docs Help https://elementscommunity.org/forum/index.php?topic=34376.msg448833#msg448833
« Reply #5 on: January 14, 2012, 12:44:29 am »
thanks for the info it was helpful to me to

 

anything
blarg: