Google Docs Help

[Sevs]

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

Name	Date	Important #
Santa Claus	12/1/2011	5
Ghandi	12/1/2011	3.2
Gary Coleman	12/1/2011	6
Santa Claus	12/2/2011	7

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

[suxerz]

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.

[Sevs]

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

[UTAlan]

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)

[Sevs]

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

[mrgoodbar64]

thanks for the info it was helpful to me to