#### Match formula to match values in multiple columns

Hi all,  does any friend know that how can I make below formula work
MATCH(A2,\$K\$2:\$M\$30,0)
 0
K
4/21/2010 11:24:43 AM
excel.programming 6508 articles. 2 followers.

2 Replies
1589 Views

Similar Articles

[PageSpeed] 45

I am not sure I have understood correectly. Please go through the below example

With data as below if you need to retrive the name of the 1st Rank holder
from London.

D2 = 1
D3 = London

In D4 apply the below formula
=INDEX(\$B\$2:\$B\$9,MATCH(1,(\$A\$2:\$A\$9=D2)*(\$C\$2:\$C\$9=D3),0))

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

ColA	ColB	ColC
Rank	Name	Office
1	Tom R	Bath
2	Katy C     	Bath
3	Nigel G   	Bath
4	Pete R     	Bath
1	Tony A	London
2	John B     	London
3	Mary C	London
4	Jane D     	London

--
Jacob (MVP - Excel)

"K" wrote:

> Hi all,  does any friend know that how can I make below formula work
> MATCH(A2,\$K\$2:\$M\$30,0)
> .
>
 0
Utf
4/21/2010 12:35:01 PM

K wrote:
>
> Hi all,  does any friend know that how can I make below formula work
> MATCH(A2,\$K\$2:\$M\$30,0)

--

Dave Peterson
 0
Dave
4/21/2010 12:38:44 PM

Similar Artilces:

Excel VB-Copy formula down until adjacent cell (left) is blank?
Here is exactly what I am trying to do through VB in Excel: Weekly data pull fills colums A:G. Row count is always different. I am modifying the data pull through VB, and I have a VLOOKUP formula in cell H2. What I want VB to do is copy that formula down column H to the last row (with data) each week. I guess I want it to be dynamic so that as rows decrease/increase the formula is only copied down to the final row/record. I know someone out of this smart group will know how to do this! Thanks in advance! Tony (pseudo code) in a macro .... dim lngLastrow as long dim rngTarget...

Matching from a form to a subform
I am currently working on a database where the user can select an outing from a form on a combo box. The outing is assigned to a particular Program (via Program name). The outing might be applicable to more than 1 program (which would then have a program name which looks like (program 1/program2) In a subform on that outing form, I have a combo box that allows the user to select members to include on that outing. I want to be able to limit the member names to only those members who have the same Program name as assigned to the Outing. I'm not clear on how to drive this in a su...

One column with different widths
I want to make Column A with rows 1-29 one width and rows 30 and above another width. -- Calpitor Excel does not allow varying widths of cells within a column (or heights within rows). You could used 'Merged' cells to accomodate the wider column needs. HTH -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15101 View this thread: http://www.excelforum.com/showthread.php?threadid=476150 ...

Sorting numbers with multiple decimal points?
Hi - I'm sure this is covered in some docs somewhere, but it's hard to know what to search for. I have a spreadsheet (using Excel 2003 SP1) in which I have a number of rows that have an "ID" field taking the format "X.X.X.X" where X is a number from 0 to 100. Like this: 1.1.0.0 blah blah rest of row 1.1.2.4 blah blah rest of row 1.1.3.0 blah blah rest of row 1.0.0.0 blah blah rest of row 1.1.3.2 blah blah rest of row 1.1.3.1 blah blah rest of row 1.1.1.1 blah blah rest of row 1.1.3.0 blah blah rest of row 1.1.1.3 blah blah rest of row 1.1.2.5 blah blah rest of ...

Need Help with Formula #3
I need help trying to come up with a formula for a friend of mine. This is what he wants -- Using any 9 numbers -- he wants Excel to come up with every possible 3-digit combination of numbers that are divisible by 7. Is there anyone who could assist me with a formula that will perform this calculation? Can Excel do such a calculation? Thanks so much for any assistance you can offer. In A1, enter the number 7. Select A1:A142. Edit>Fill>Series, Step value 7. Format>Cells>Custom, "000". If with "using any 9 numbers" you mean you don't want any zero...

Is there a quick way of assigning multiple series to a bubble char
Each series requires a three-column block of cells, one each for X, Y, and bubble size. Select the block for the first series and create the chart. Select the block for the second series, copy it, select the chart, use Edit menu - Paste Special to add the data as a New Series, with categories in the first column. Repeat as needed. If the series share the X values, you can use this sequence of columns: X Y1 B1 Y2 B2 Y3 B3 where X is the column of shared X values, Yi is the column of Y values for the ith series, and Bi is the column of bubble sizes for the ith series. - Jon -----...

A script to replace a value with another
i want to replace a field with another within the same Contact record. Is there any example that you can show me? i'll doing this to over 5,000 records. i'm using Outlook 2002, personal use. rayswchiu -- Phone: (852) 9258 6600 Email: rayswchiu@gmail.com ...

How can I default a lookup field to a particular value.
How can I default a lookup field to a certain value, but still allow the user to modify the field if necessary? Thanks, Bob Johnson you mean a drop down or picklist? "Bob" <rjohnson@reveregroup.com> wrote in message news:1130947400.655495.115390@f14g2000cwb.googlegroups.com... > How can I default a lookup field to a certain value, but still allow > the user to modify the field if necessary? > > Thanks, > > Bob Johnson > i have the same question in crm 3.0. i want a default value in a lookup filed. i also want to create a lookup field by my self....

Non-VBA formula to find 2nd Sunday of a given month
Can anyone help me write a formula to find the date of the second sunda in a given month? Thanks in Advance, Dav ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com David, Assuming you have a date in A1, this gives the 2nd Sunday of that date =DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)+7 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "DavidObeid&q...

How to represent a column in Excel
Hi, everyone I currently have a formula. {=SUM(IF((Data!G2:G75="Verified")*(Data!K2:K75<=7),1,0))} However, the length of both column G and K are not fixed. The data is retrieved from a remote database. Is there any other way to represent it ? Thanks One quick-and-dirty way is to just do the whole column, x`like: > {=SUM(IF((Data!G2:G65536="Verified")*(Data!K2:K65536<=7),1,0))} It won't be appropriate for Excel 2007, but is there any chance at all you'll actually fill the sheet to row 65536? It's also considered somewhat sloppy, but you'...

I need to write this formula in basic
Hi! Does anyone know how to write this formula in Basic. I need to make it work in Openoffice because Excel seems to crash with a list of 20000 people. =IF(ISERROR(VLOOKUP((RC1&R1C),Sheet1!R2C1:R45000C4,4,0)),"""",VLOOKUP ((RC1&R1C),Sheet1!R2C1:R45000C4,4,0)) Thanks!! ...

CRM & Data Matching
Hi, We are going to populate our MSCRM with various data sets. Because of this there will be duplication of customer data. Is there any mechanism within Microsoft CRM (or easily available elsewhere) to match the data from the diverse sources against each other and to arrive at a single instance of the customer. Thanks, D. Microsoft CRM V3 only offers merge capabilities which means you have to locate the duplicates - crm can then merge the records two at a time. ISV's have produced other solutions ======================= John O'Donnell Microsoft CRM MVP http://codegallery.got...

Setting multiple meeting times for different attendees on the same day
I am a recruiter and use Outlook to schedule interviews with out of town candidates. It's critical that all my interviewers be available on the same date but at different times during the day. I would like to be able to pull up all of my interviewers' schedules at the same time and schedule them in different time slots but only having to send one meeting request. Example: I have 5 managers that will be interviewing the same person on the same day at different times. I have to list them all as "required attendees", check their schedules, then back out and send o...

Functions & Formulas
Hello, I like to see if anyone is able to help me come up with a formula or function that will do the following for me: I have a set of 9 computer generated (somewhat random) numbers. THESE NUMBERS WILL NOT BE IN AN ASSENDING OR A DECENDING ORDER. Example is something like the following set of numbers: 18.34, 19.37, 20.4, 19.38, 17.96, and so on, up to nine numbers. I CAN ORDER THESE NUMBERS IN A ROW OR A COLUMN, EACH IN A SEPARATE CELL. HOWEVER, THIS IS HALF OF THE PROBLEM!. NOW I have another number, I WILL CALL THIS NUMBER MY CONTROL NUMBER. This control number (19.11...

How to print a multiple page two sided small booklet, 4.5x5.5"
Publisher 03 has a folded card that uses standard 8.5x11" paper and after printing folds to a card size 4.5 x 5.5" What I need is to create a non folded "booklet" version which can have multiple pages (more than 8), printed on front and back. Essentially the same as the 8.5x5" program booklets (found in File/Page Setup/publication type:booklet) which print multiple pages now, but smaller and capable of printing front to back pages to either be folded into a booklet (one fold on center pages) or cut and bound into a booklet. I'm trying to build a small boo...

Pivotchart Multiple series question
Is it possible to have 2 separate clustered series in 1 pivotchart table and if so how. Regards Jason ...

x values and plotting points
I need to plot points and draw a straight line to calculate the slope. I will use this same method to present a trend for data points over time ( three years per compound)! Help! I have Excel 97. Thanks! annette christian wrote: > I need to plot points and draw a straight line to > calculate the slope. I will use this same method to > present a trend for data points over time ( three years > per compound)! > > Help! I have Excel 97. > > Thanks! You should be able to chart your data points using the Chart Wizard. See http://www.geocities.com/jonpeltier/Excel/...

Help with formula containing text
Hi I need some help on the following. I have a column of text, linked to other worksheets, that is continuously changing. I need to be alert if the same piece of text appears in the column more than twice, e.g. Tom Tom Jane Mary Mary Tom "ALERT" Thanks Mike -- mlhynes ------------------------------------------------------------------------ mlhynes's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=12959 View this thread: http://www.excelforum.com/showthread.php?threadid=401787 Mike Visit Chip Pearson's site for much help on duplicates. Fin...

Value creation failed at line 422
Does anyone know the source of this error message and how to fix it? well, you have to provide more info than that before people here could provide possible solutions. Info like, what GP application? what window? what module? what are trying to do on that window? posting? printing? saving? do you have customizations? ---Darryl Bajaro "Phil" wrote: > Does anyone know the source of this error message and how to fix it? ...

making a sum outcome negative based on adjacent cell value
I have 3 columns of single figures. At present i'm using the sumproduc fuction to multiply and total the figures in column A that fall betwee 4 and 9 with the adjacent figure in column B... =SUMPRODUCT(--(\$A1:\$A600>=4),--(\$A1:\$A600<=9),(\$B1:\$B600),(\$A1:\$A600) I'd like to add column C to the formula, so that if it contained value of -1, 1 or 2, the sum of the adjacent figures in columns A and appears as a negative number. For example A3= 7, B3= 2, C3= 1 Outcome= -14 A4= 9, B4= 1, C4= 5 Outcome= 9 A5= 3, B5= 2, C5= 2 No sum because figure in column A ...

Match Duplicate Accounts doesn't work?
I just recently upgarded my Money 2004 to 2006 Deluxe. To take advantage of new features, I started setting up the online services for my checking account. I was able to have Money establish a connection w/ my bank and login w/ my supplied username and password. Then I got to the part where Money asks "Are you already keeping track of any of these accounts ..." - Yes - No - I'm not sure So, I select Yes because I have indeed already set up that account (and have many years worth of data in it!). Then I click thex Next buton whic...

getting info from a pull down menu and looking up column to get co
Sorry I don't know all the jargon, but basically i have made a list called employee and used it to make pulldown menus in multiple worksheets to enable me to select an employee for labor costs. Now I want to lookup(from the pull down menu,another list I made Called name, another list i made call rate) to be entered in the rate column. The "name" list='Employee records'!\$A\$3,'Employee records'!\$A\$4,'Employee records'!\$A\$5,etc... The "rate" list='Employee records'!\$C\$3,'Employee records'!\$C\$4,'Employee recor...

VBA Range Formula
Can anybody tell me how to get this to work? In other words, how do I reference my procedure variables in a cell formula? Thanks! Sub Marktest() Set b = Cells.Find("Total", , xlValues) Set c = Range("C5") Range(b.Offset(-1, 2), c).Formula = _ "=sumproduct((\$B5=Range(b.Offset(-7, 1), b.Offset(-5, 1)))*\$C\$1:\$C\$3)" End Sub I'd do something like: Option Explicit Sub Marktest2() Dim myRng As Range Dim b As Range Dim c As Range With ActiveSheet Set b = .Cells.Find("Total", , xlValues) Set...

rounding in multiples of 5
Hi, How do I ensure the cell content is always a multiple of 5 without running a macro? I currently have the following formula in the cell, which only works to modify if the contents are below 5: =IF(C6>0,IF(C16+C17<5,5,MROUND(C16+C17,5)),0) Can someone help? Thanks! USe data validation on cells where you want the user to enter the values that are multioples of 5 by selecting Custom and entering the following: - =MOD(A1,5)=0 If the result is not zero it will give an error message "Jason" <none@nowhere.com> wrote in message news:ORhv%23lSiEHA.1644@tk2msftngp13.phx...

Collecting Range Name values to VBA
I have a worksheet "Setup" where users type in a date in a cell named "ChtDte" and a path and database name in a cell named "FLName". I am using DOA to connect to a database and return a record set. The query used "qryCOCostwRates" uses a date paramater. Because this sheet will be used by several users all pointing to the database in different locations, I need to know where they have the database. I need to get the values in these two range names in the setup tab of the spreadsheet so I can connect to the data, and provide a value for ...