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! 

0
Utf
2/4/2010 5:11:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
2894 Views

Similar Articles

[PageSpeed] 9

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! 
> 
0
Utf
2/4/2010 5:19:01 PM
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" <dcw0405@discussions.microsoft.com> wrote in message 
news:30F655DA-EC62-4FBA-842B-41510DC10BFF@microsoft.com...
> 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!
> 


0
T
2/4/2010 5:23:12 PM
Reply:

Similar Artilces:

saving with name from cell
Hi all. I'm trying to place the contents of a cell (or two) into the save a name when saving a workbook. Anyone know of a simple way to accomplish this? Thanks -- jama ----------------------------------------------------------------------- jamaz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1663 View this thread: http://www.excelforum.com/showthread.php?threadid=31504 You can use this jamaz ActiveWorkbook.SaveAs Sheets("mysheet").Range("d1").Value & ".xls" Or ActiveWorkbook.SaveAs Sheets("mysheet").Range...

Lookup button in named printer run-time selection window
I found this information about Named Printers in the managed newsgroup, and it works exactly as stated: "Under the Company Series you will find a Custom Report Printer which can be used to define the printer for all custom reports. If you want to be able to choose the printer you can, select a printer class but leave the Printer ID blank... this will ask for a printer ID of the appropriate class." I can use this solution with a client needing to print two different versions of their receivables statements, but I can see a potential improvement. My suggestion is to add a lo...

Transfering Outlook messages from one computer to another.
I want to transfer all of emails and folders from my old laptop to the new one. Is it possible? And what is the best way to do it? I am currently using Microsoft Outlook Express 5 to retrieve my email messages from Earthlink onto my current laptop. Hi, Samuel - This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Internet Explorer and is a quite different program, despite its similar name.. You will probably get a faster and more expert answer if you post this to an Outlook Express news group. Try posting in one of these newsgroups: mic...

Two tables same name?
When I go to resource descripitions, and looked under sales tables I notice there is two table physical name exactly the same? RM Open File Physical name RM20101 RM Open File Debit Copy File Physical name RM20101 Just wondering has RM20101 can be placed in two different area's at the same time. I didn't think this was possible. Thanks for your help. When I go into resource descripitions, and look under sales I notice there is two physical names exactly the same? RM Open File Physical name RM20101 RM Open File Debit Copy...

Query based on two sets of depth intervals
I have two tables (this all has to do with drilling). The first table consists of a drill hole label, from depth, to depth, and geology (i.e. what rock is present at that interval). My second table has a drill hole label, sample number, from depth, to depth, and nickel content. I want to be able to query out what geology is present (from table 1) for each sample (table 2) interval. The geology intervals do not necessarily match the sample intervals. I'm unsure how to code this. Is there anyone out there who could give me some pointers? Anything I've tried so far has given...

Complex value lookup? (Excel 2003)
I am setting up a cost calculation workbook. For the purposes of this discussion, it will have two worksheets: VARIABLES and COST CALCULATIONS. Some of the items on the VARIABLES sheet have multiple prices with price break tiers. E.g. (totally made up prices below) Software #Licenses (up to) Price Adobe Acrobat 10 $500 Adobe Acrobat 25 $450 Adobe Acrobat 50 $400 Adobe Acrobat ...

dialog template from one project to another
How the heck do you "export" a dialog template from a project for use in another project? >How the heck do you "export" a dialog template from a project for use in >another project? If you open both project's RC files in the IDE, you should be able to copy/paste the dialogs. Dave -- MVP VC++ FAQ: http://www.mvps.org/vcfaq BobF wrote: > How the heck do you "export" a dialog template from a project for use in > another project? If the dialog template is in project_2, open its resource file in project_1 and copy/paste. It's kind of trick...

Another address list ?
Hi - I am running Exchange 2003 on Win2003 SBS edition. Could someone tell me whether it is possible to have another address list which exists separate to the GAL ? So for example if you compose a new message and click on the To button, I would like the users to see the GAL and also another entry underneath saying "External Emails" which will have the external user email addresses in it" How do you go about doing this ? Thanks in advance You would create this in the Address Lists container in Exchange System Manager. "Edward" <dont-mail@me.com> wrote...

Array of ints of variable size
Hi, how can I make an Array of int's with no fixed size, that is a variable lenght arrray of ints? Is there any MFC class to achieve so? thanks Yes, you can use the CArray template class. Find the right includes and the declare as: CArray<int, int> MyIntArray; -- Jonathan Wood SoftCircuits Programming http://www.softcircuits.com "Martin" <mrbiancu@gmail.com> wrote in message news:1159913290.211472.8370@m73g2000cwd.googlegroups.com... > Hi, how can I make an Array of int's with no fixed size, that is a > variable lenght arrray of ints? > Is there a...

Format Row Based on Cell Value Selection
Try to figure out how to write a macro button to color all rows "yellow" that match a selected cell's value in 2 ranges. Specifically, I want to be able to select a cell in column A from sheet1, click a macro button, and highlight every row in 2 ranges "Players1" and "Players2" that contains the value from the selected cell in the first column of either range. Also, wondering how to undo Thanks Hi Mike, How are the ranges named? Are they named in the interactive mode with Define Name or are they named in a macro with Set rnge = range etc? Can you pro...

Dictating a field value based on the value of another field -OnCha
Hello all and thanks in advance for any help you can give me. I know next to nothing about Java scripting, so I need to be hand-held a little bit here. I'll provide as much detail as I can in the hopes that someone can tell me what to write into the box provided. Using CRM 3.0 Opportunity Main Form - I have two fields - one is "probability" and one is "salesstagecode". I want to drive the value in the probability field based on the value in the salesstagecode field. For example, if salesstagecode="80%-Proof" then probability=80. if salesstagecode-&...

Add a comma at the end of a word in a cell
I have cities in a column and I would like to add a comma at the end inside the cell. how do I do that? Say your cities are in column A. In a helper column, enter =A1 & "," and copy down. Then copy the helper column and paste/special value back into your original column. -- Gary's Student "Angela" wrote: > I have cities in a column and I would like to add a comma at the end inside > the cell. how do I do that? I'm assuming you want a formula for this. With the city in A1: =A1 & ", " The formula above also includes a space afte...

Putting last three cells with data in a row into a set three cells
I have three columns of data per month. (Jan=RST, Feb=UVW, March=XYZ, etc for 5 years) and I want to look across rows 16-200 and copy the last month (3 columns) with data in it into the columns OPQ. Specific issues: I want the data pasted with values only because the first two columns of every month have data validation. I want all the data from rows 16-200 pasted even if they are blank so they will cover over the data that was there from the previous month. The cells that are not used are blank, but the last column for each month has a formula in it: =IF(OR(R16="",S16="&qu...

Lookup Formula Needed
Here is what I need the formula to do: I will have two columns, Column A will have a value and Column B will have a code. I want to Sum the numbers in Column A IF the Code in Column B has the letter "R" in it. So, using the data below, my formula would add the values 100+400+500 from column A and return a result of 1,000. Any help would be appreciated! Column A Column B 100 R1 200 M3 400 R2 500 R6 700 I1 300 M3 Try this: =3DSUMIF(B:B,"*R*",A:A) The asterisk ...

Cells to link to "current workbook"
I have a spreadsheet that contains pricing that is calculated by referencing cells in various other workbooks saved under customer names (not at the same time). I need to create a macro that will allow me to easily link my pricing workbook to my customer workbooks. Any macro that I have tried to create to accomplish that limits itself to the workbook I pointed it to while creating it. Is there a way to write the code that can be flexible to reference any workbook and not specifically the workbook it originally referenced? Hope that made sense. I'm stuck. I've tried all kinds...

How to look up a value in a list and return multiple corresponding
I followed the instructions on the excel help page of how look up a value in a list and return multiple corresponding values but somethings not right. I have tried this numerous times in my excel spreadsheet and it does not return any values let alone numerous values. No values will appear unless I go into the insert menu and click function, but then here it will only give me the smallest value, even when there is more than one. Please help asap! Thank you. How about giving us some details? -- Biff Microsoft Excel MVP "123456789" <123456789@discussions.m...

VLookup Returning N/A
Hello: I having a problem with a VLookup formula. My main spreadsheet is a download from a mainframe and the lookup table (on a separate spreadsheet) is inputted directly into Excel. The lookup value is a PO number. All the VLookup Formulas are returning N/A. The weird thing is that if I manually type in the PO Number in the main spreadsheet, then the formulas work. Or if I copy the PO Number from the main spreadsheet into the lookup table, then the formulas work. Obviously the PO numbers in the main spreadsheet and the PO numbers in the lookup table aren't matching, but I can't f...

in-cell dropdown formatting
In Excel I sometimes use data "validation" with a "list" to provide me with a drop-down list of possible entries for a cell, which can be quite useful for repetitive stuff. Unfortunately, I have not discovered how to control the font size within the drop down, and this occasionally becomes tiny and illegible. Does anybody know how to alter the drop-down text size? Typically I might have a list of 20 names. I don't think you have any control over the font size of the validation drop down list. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software ...

Extract string from cell
I have a column of data as follows: JOHN SMITH#INTERN#3154#jsmith@xyz.com# |\ JANE DOE#SPECIALIST#2312#jdoe@xyz.com# |\ . . . I would like to extract the e-mail alias, in the above examples it would be "jsmith" and "jdoe". I'm familiar with the LEFT, MID, and RIGHT functions, but those won't work because the location of the string I want t extract is not fixed. Any ideas would be appreciated. Thanks! If it is always the third # that is preceding the email and there is always a # |\ at the end...

Pivots: Grouping two fields (year, month)
My pivot table contains two fields I need grouped: "Fiscal Year" and "Fiscal Month." Both field are numeric, not dates. The grouping would be like this: Fiscal Year (2003) and Fiscal Months (9-12) GROUPED WITH Fiscal Year (2004) and Fiscal Months (1-3) Is there a quick way to do this within the pivot? If you group the "months", the table groups all the months regardless of "year"....can't figure this one out. TIA... John, The easiest way would be to have another column in your data table indicating the desired grouping: you could use a formula t...

Non Printing of Cell borders
Hi I have been asked to do a small xcell spreadsheet invoice layout for a local charity To make the layout simple to use i have a border around some input cells However the charity use preprinted invoices so i dont need the cell borders printed Is there a way to keep the border onscreen and not have the cell borders printed thanks -- paul007 ------------------------------------------------------------------------ paul007's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28462 View this thread: http://www.excelforum.com/showthread.php?threadid=480793 You c...

How can formulas be used to create another formula?
This is probaby very simple, but I really don't know a lot aout formulars. When I have created formulas to work out the percentage of each sales type that we have, I then need to use this % as a part of another formula, Ie Sales type A is 50% of our overall sales "=sum(B1/B3)*100", I am am then trying use this 50% to apportion our wages bill to a sales type. When I create another formula "=sum(B10/B13)*the cell reference of the formula I previously calculated" it is throwing out the incorrect answer. Can anyone give me a simple answer to a very simple question a...

Function of Print To Slip receipt option?
Hello, I've been looking for what exactly "Print To Slip (OPOS)" on the receipt properties window in RMS Manager does, and I have yet to find an answer. Can anyone give me an explanation? Thank you. Data, If you have a slip receipt printer, it will print info on the slip receipt instead of the smaller 40 paper. Some examples of slip printers; http://tinyurl.com/rahka http://tinyurl.com/o5jbp -- * Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not usi...

Wrong URL returned by GetCrmServerUrl after server move
We have moved our CRM 4.0 front-end from oldserver to newserver The GetCrmServerUrl function called by AuthenticationPipeline still returns oldserver Why? CRM 4 has been uninstalled, no trace of oldserver in config files, registry or database. CRM Deployment shows only new server Meanwhile, both C360 applications and Stunnware Tools crash in trying to connect to oldserver with No connection could be made because the target machine actively refused it oldserver_ip Where this this metadata stored to keep reference on server removed from deployment ? Thanks for your help ...

transpose function creating zeros
I have a named area where I am copying data from and some of it has data , some is empty. It is a mixture of textual, numeric and date values.. otherwise the cells in a column are empty BUT when I use transpose to copy this area it creates lots of zeros in the area not being written to. How can I get it to copy properly. Thanks. --- Message posted from http://www.ExcelForum.com/ One way: =IF(TRANSPOSE(namedrange)<>0,TRANSPOSE(namedrange),"") In article <busyman5_au.12483o@excelforum-nospam.com>, busyman5_au <<busyman5_au.12483o@excelforum-nospam.com&g...