Excel 2007 : SUMIFS without the sum part

[aldek]

Hi,
I am trying to use a formula to retrieve information from a database. The formula I have works fine if I want to sum the information which match the criteria, but I don't want to sum it.

Here is the formula: =SUMIFS(INDIRECT(G503),date1,D504,store,D505)

So "date1" and "store" are both named ranges. I just want to find the unique (or first) item which matches the above info.

I have been working on trying to figure this out all day, please help! Thanks!

[TMS]

Maybe VLOOKUP is the function you're looking for.


Regards, TMS

[aldek]

Hi TMShucks, thanks for the quick reply!
The issue I have with both the index and vlookup functions is that I need to know the position of the data relitive to one of the conditions. Perhaps I lack imagination, but I don't know how to make vlookup work for this.

[dip11]

This is very similar to the question I posted a few days back and am still waiting for a final solution for. A temporary solution courtesy of Marcol has already been posted which would do what you want:
http://www.excelforum.com/excel-2007...ted-value.html

[daddylonglegs]

Try like this

=INDEX(INDIRECT(G503),MATCH(1,(Date1=D504)*(store=D505),0))

confirmed with CTRL+SHIFT+ENTER

[aldek]

Daddylonglegs, this is perfect. Exactly what I need, thank you so much!
Dip11, thanks for the reply, maybe the above formula will help you. Thanks again,
Aldek