Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup function for two variables and return another cell?
Hi! I am looking for some help. Seems pretty simple but I can't seem to
figure it out. I know I'll need a lookup function and an "AND" function of some sort... Take a look and let me know what you think. row/column A B C D 1 New SSN 1st signed Blue 2 2 New SSN Calc - not signed Green 1 3 Previous SSN 1st signed Red 3 So I need help finding the combination of "New SSN" and "1st signed" and I need it to return the corresponding number. So for example, this one would return the number "2". Any suggestions? Basically I need a formula that looks up "New SSN" in column A and "1st signed" in column B and then return the corresponding value in column D. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup function for two variables and return another cell?
Hi,
Try this ARRAY formula. The 2 lookup values are in E1 & F1. See below on how to enter and array formula. =INDEX(D1:D3,MATCH(1,(A1:A3=E1)*(B1:B3=F1),0)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "dcw0405" wrote: Hi! I am looking for some help. Seems pretty simple but I can't seem to figure it out. I know I'll need a lookup function and an "AND" function of some sort... Take a look and let me know what you think. row/column A B C D 1 New SSN 1st signed Blue 2 2 New SSN Calc - not signed Green 1 3 Previous SSN 1st signed Red 3 So I need help finding the combination of "New SSN" and "1st signed" and I need it to return the corresponding number. So for example, this one would return the number "2". Any suggestions? Basically I need a formula that looks up "New SSN" in column A and "1st signed" in column B and then return the corresponding value in column D. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup function for two variables and return another cell?
Assuming that the combination of New SSN and 1st signed is unique...
If you're using Excel 2007: Use cells to hold the criteria... F1 = New SSN G1 = 1st signed Then: =SUMIFS(D1:D10,A1:A10,F1,B1:B10,G1) This one will work in any version of Excel: =SUMPRODUCT(--(A1:A10=F1),--(B1:B10=G1),D1:D10) -- Biff Microsoft Excel MVP "dcw0405" wrote in message ... Hi! I am looking for some help. Seems pretty simple but I can't seem to figure it out. I know I'll need a lookup function and an "AND" function of some sort... Take a look and let me know what you think. row/column A B C D 1 New SSN 1st signed Blue 2 2 New SSN Calc - not signed Green 1 3 Previous SSN 1st signed Red 3 So I need help finding the combination of "New SSN" and "1st signed" and I need it to return the corresponding number. So for example, this one would return the number "2". Any suggestions? Basically I need a formula that looks up "New SSN" in column A and "1st signed" in column B and then return the corresponding value in column D. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return multiple values using a lookup function | Excel Worksheet Functions | |||
Lookup based on two variables and return a third | Excel Worksheet Functions | |||
Lookup function - can it return the higher value? | Excel Discussion (Misc queries) | |||
Lookup function using 2 variables | Excel Worksheet Functions | |||
return range of data from lookup function | Excel Worksheet Functions |