Search for one answer on multiple criteria (excel 2003)

[ahwahneeliz]

What bothers me the most is I had this before, but the file got deleted.
Anyway,
By entering a number in each of two cells, I need to look up an exact
quantity (number of people from 1 to 8) from column 1, a less than or equal
to value from column 2, and display the matching item from column 3. If you
need to make more columns or whatever, feel free. This is simply a table for
people who qualify for a lunch program. If there are a certain number of
persons in the household, and the household earns less than X$, then they
qualify for F, R, or D do not qualify. I don't mind if it looks up for the D
value, or just assumes the D value because of a false result.


a1 a2 a3
Persons in House income category
1 1100 f
1 1300 r
2 1250 f
2 1400 r
3 1350 f
3 1680 r
and so on.
Thanks in advance. I've been pulling my hair out trying to recreate the
formula.

 

[Luke M]

There's probably a way to do this more efficiently, but this worked for me:

=IF(ISERROR(INDEX(C2:C7,SUMPRODUCT((A2:A7=D2)*(B2:B7>=E2)))),"D",INDEX(C2:C7,SUMPRODUCT((A2:A7=D2)*(B2:B7>=E2))))

Number of people in households listed in D2, income listed in E2.

Hope it helps!

 

[Sheeloo]

What you can do
(do test it out ...)
Insert Col C and enter
=A1&B1 and copy down till end of your data set

Now suppose you have no of people in C1 and earning in D1, put this in E1
=VLOOKUP(F1&G1,C,2,TRUE)

#N/A will be your FALSE condition

You can use this to suppress that
=ISNA(=VLOOKUP(F1&G1,C,2,TRUE),"D",=VLOOKUP(F1&G1,C,2,TRUE))

 

[Sheeloo]

You can also try
=INDIRECT("C"&SUMPRODUCT(--($A$1:$A$6=F1),--($B$1:$B$6>G2),--(ROW($1:$6))))

Adjust the 6 to your last row.

It will give 0 when no match is found.

 

[ahwahneeliz]

Not quite... unless I'm doing it differently than you intended (and I
probably didn't explain it well). eg, 1 person, >=1300 income, R result. But
in your equation if I have 1 person, and 1305, I still get the F as a result.
At that point I need it to give me the R. Essentially, depending on the
number of people, and thier household income, they end up in one of three
categories: F, R, or D for denied.
Thanks for your quick responses though.

 

[ahwahneeliz]

I appreciate your suggestions, both of you, but I'm still not able to get the
right answer. Yes, I've been "playing" with this off and on ever since I
originally asked the question. If you look at the data entered closely, you
will perhaps see why it's not working. I vaguely remember using the vlookup
or hlookup, and using the * between the two parts of the selection criteria
equation.
Thanks...
Liz (hopefull or hopeless?