Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for one answer on multiple criteria (excel 2003)
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for one answer on multiple criteria (excel 2003)
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! -- Best Regards, Luke M "ahwahneeliz" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for one answer on multiple criteria (excel 2003)
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:D,2,TRUE) #N/A will be your FALSE condition You can use this to suppress that =ISNA(=VLOOKUP(F1&G1,C:D,2,TRUE),"D",=VLOOKUP(F1&G 1,C:D,2,TRUE)) "ahwahneeliz" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for one answer on multiple criteria (excel 2003)
You can also try
=INDIRECT("C"&SUMPRODUCT(--($A$1:$A$6=F1),--($B$1:$B$6G2),--(ROW($1:$6)))) Adjust the 6 to your last row. It will give 0 when no match is found. "Sheeloo" wrote: 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:D,2,TRUE) #N/A will be your FALSE condition You can use this to suppress that =ISNA(=VLOOKUP(F1&G1,C:D,2,TRUE),"D",=VLOOKUP(F1&G 1,C:D,2,TRUE)) "ahwahneeliz" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for one answer on multiple criteria (excel 2003)
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. "Luke M" wrote: 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! -- Best Regards, Luke M "ahwahneeliz" wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for one answer on multiple criteria (excel 2003)
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct & multiple criteria - one answer | Excel Discussion (Misc queries) | |||
Counts for Excel 2003 using multiple criteria | Excel Worksheet Functions | |||
Lookup Multiple Criteria return One answer | Excel Worksheet Functions | |||
How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
Select by multiple criteria (Excel 2003) | Excel Worksheet Functions |