# Thread: Retrieve data based on multiple inputs (2007)

1. ## Retrieve data based on multiple inputs (2007)

I’m looking for a way either using Sumifs, strictly vlookup, or some way to retrieve data based on multiple selections.

I’ve attached a sample spreadsheet as an .xls file, but am using 2007 which can utilize the Sumifs formula.

The example shows a selection of John in July with a result of 70. I’d like it to automatically total this for me based on the selections I’ve made.

And if there is a blank selection, I’d like it to total based on the single selection. For example, choosing “Jan” only would result in “100”.

I’ve tried various forms of vlookups, indexing, and even sumifs. I keep running into a dead end.

Thanks in advance for the help!

2. ## Re: Retrieve data based on multiple inputs (2007)

For example:

=INDEX(B2:E13,MATCH(I2,A2:A13,0),MATCH(H2,B1:E1,0) )

3. ## Re: Retrieve data based on multiple inputs (2007)

Or this:

=VLOOKUP(I2,A2:E13,MATCH(H2,B1:E1,0)+1,FALSE)

4. ## Re: Retrieve data based on multiple inputs (2007)

And another one:

=SUMPRODUCT(B2:E13*(A2:A13=I2)*(B1:E1=H2))

5. ## Re: Retrieve data based on multiple inputs (2007)

Thank you for the quick reply.

Both of those work if there is a selection in both cells, but if there is only one, I get the #N/A error.

Is it possible to retrieve the total if there is only one selection?

6. ## Re: Retrieve data based on multiple inputs (2007)

You could use this array formula (confirm with Ctrl+Shift+Enter):

=SUMPRODUCT(B2:E13*IF(ISBLANK(I2),1,A2:A13=I2)*IF( ISBLANK(H2),1,B1:E1=H2))

7. ## Re: Retrieve data based on multiple inputs (2007)

Hey thanks. That works.

I found out when transfering it to my real spreadsheet that because I have some #N/A results in my table, the formulas and array won't work. At least that's what I suspect. And that's probably why I kept getting bad results in the first place.

I'll keep messing with this and if nothing else I can have it pull from some duplicate data that does not have any #N/A results.

Thanks again.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•