Is there any way to use the MATCH function with more than 1 column

For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value 
215, but my data set spans more than one column. 

My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) 
returns an #N/A error. What can I do?
0
MrSnrub (7)
5/22/2005 3:56:01 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
594 Views

Similar Articles

[PageSpeed] 51

Do you want both the row and column ? Two separate values?
-- 
Gary's Student


"Mr. Snrub" wrote:

> For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value 
> 215, but my data set spans more than one column. 
> 
> My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) 
> returns an #N/A error. What can I do?
0
GarysStudent (1572)
5/22/2005 1:53:16 PM
yes
0
MrSnrub (7)
5/22/2005 4:08:02 PM
In cell A52 enter =MATCH(215,A3:A51,0)
Then copy this formula from B52 through Z52.
If there is only one instance of 215 in the data area, then all the formulae 
will show #N/A except one.

For example, if the 215 is in cell C15, then C52 will show 13.  Thus we know 
that the 215 is in column C and is in the thirteenth row of the data block 
(fifteenth row of the worksheet.
-- 
Gary's Student


"Mr. Snrub" wrote:

> yes
0
GarysStudent (1572)
5/22/2005 5:37:34 PM
great. thank you.
0
MrSnrub (7)
5/22/2005 5:46:15 PM
Reply:

Similar Artilces:

Retrieving Data from a Column / Row to populate a cell
I currently have data supplied to me in an Excel spreadsheet that I to transfer manually I would like this to populate a cell reference, the problem I have is that I cannot gather a train of thought to obtain the data from from a specific row/column. There is a sample of the data supplied to me below Name In Out Int Greg 165 108 29 09 July 2003 42 34 9 14 July 2003 44 19 4 16 July 2003 40 40 8 17 July 2003 39 15 8 Andrew 125 43 9 1...

Copy Cell or Column without advancing formula reference?
I have a spreadsheet with formulas referring to another worksheet. I need to copy the columns however, each time I do, the formulas advance to another cell. Which is an incorrect reference, skewing my results. You need to modify the formulas to use absolute references. Edit each of the cell addresses in the formula and put $ (dollar signs) in front of the column letters. "sgluntz" wrote: > I have a spreadsheet with formulas referring to another worksheet. I need to > copy the columns however, each time I do, the formulas advance to another > cell. Which is an...

Change the space used for my x-axis labels
Can anybody help me? I have an column chart with words as labels. My problem is that I can't change the space used for the labels, that is some labels is in two or three lines and I would like to use more room for the labels and less room for the chart itself. But when I make the chart smaller then the labels just are dragged upwards - how do I prevent that?? ...

using sumproduct with a substring condition?
I have a calculation I just made that looks like this: =SUMPRODUCT(--(C5:C50="persons name"),I5:I50) which works out the hours billed from column I and works fine. Column E has a description in it which sometimes has at the end of the description the words "do not bill." Is there any way to add this condition to the sumproduct - i.e. exclude from the sumproduct any value in column I that has the "do not bill" sub-text in column e? Thanks, Ben >exclude from the sumproduct any value >in column I that has the "do not bill" Try it ...

RFX_Text converts empty strings to NULL when using ODBC and MFC
When submitting updates to the database and the column has a Not NULL constraint I get an error from the ODBC driver. It appears that the field exchange mechanism is converting the empty strings to NULL. I have seen this topic posted before, however, the solution was never presented with the post. Thanks:-) ...

Independently Formatting Columns and Rows
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Is there a way to independently format the width of a column on specific cells? In other words, if I wanted to change the width of the cells in the middle of the document, can I do so without affecting the width of the cells above it and below it? On 2010-05-28 12:40:38 -0400, monks617@officeformac.com said: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: > Intel Is there a way to independently format the width of a column on > specific cells? In other words, if I want...

Using a two-dimensional array in Excel 2002 or 2003
I am trying to improve my coding skills in Excel VBA and I just can't seem to find a good, very basic, example of how to use a 2-dimensional array. I've looked in several reference books & browsed VBA Forums but can't find an example I can understand. What I was trying to do was define a 3 x 4 Array that has a data type of Single. My sample data is located in Cells E1:H3. E F G H 1 3.4 8.1 11.6 100.3 2 5.2 7.3 45.2 632.4 3 1.7 9.6 39.7 161.5 I've been trying to use the LBound and UBound functions too, thinkin...

add functionality to RMS calendar
the calendar is a good function to have. Expanded functionality would be great. Design it as a watered down version of Outlook - set individual appointments that can be assigned to individual customers. That way you can search the calendar to see what appointments individual customers have scheduled. Being able to set up recurring schedules would be helpful, too. If it doesn't make sense to expand the calendar functionality, then create functionality to synchronize customer list in RMS with Outlook. FRB ---------------- This post is a suggestion for Microsoft, and Microsoft resp...

sheet 1 in my spreadsheet is green. What have I done?
Only the first sheet of my spreadsheet file (4pages) has gone green. I'm not sure what I have done and tried the formatting and the fill options and can't seem to find what the problem is. The cell lines are visible through the colour, but I have not made changes to the background either...does anyone know what could be the problem??? ...

Aggregate Functions
Hi I'm looking for the best way to implement a Last and First function on group queries: This is an example table mytable p_id dattime xval --------------------------------------------------- 22 2010-04-12 00:00:00 20 22 2010-04-12 01:00:00 30 22 2010-04-12 03:00:00 15 22 2010-04-12 07:00:00 25 22 2010-04-12 23:00:00 22 With this query I get sum, maximum and minimum values in the period: select p_id, max(xval) as max_value, min(xval) as min_value, sum(xval) as tot_value from mytable where p_id = 22 and date...

Selecting the font size matching the control's height
Hello, I have an array of labels, which height changes in relation to the form's height (form is resizable). What will be the best method of selecting the labels caption font size? Thanks, Claire On 7 Feb, 04:46, "Claire" <replyto@fra> wrote: > Hello, > =A0 =A0 =A0 =A0 =A0 =A0 I have an array of labels, which height changes i= n relation to > the form's height (form is resizable). > What will be the best method of selecting the labels caption font size? > Thanks, > Claire Might be an answer here http://www.dreamincode...

=?ISO-8859-1?Q?Re_=96_GGVT_Excel_and_Engineering_Application?=
Re � GGVT Excel and Engineering Application 1. A series of piping line nos. is listed as follows :- Line no. 25-UA-4401-1141 Line no. 50-PD-3218-6143 Line no. 50-VL-2210-1149 Line no. 80-SW-4020-1706 Line no. 100-DH-2855-1143 Line no. 100-IA-4304-1143 Line no. 150-PG-1438-6143 Line no. 150-VA-2500-1143 Line no. 250-PL-1250-6143 2. The line nos. are characterized by the service identifiers (namely, UA, PD, VL, SW, DH, IA, PG, VA and PL). 3. How could the line nos. be sorted according to the service identifiers in an alphabetical order ? There's a lot more line nos. to do than being pres...

Using a formula in a Hyperlink Cell reference
I have a workbook with approx 60 worksheets in it. I have set up hyperlinks on the 1st sheet to take me directly to the worksheet I want to see. Is there any way of using a function in the Hyperlink cell reference, so the link will automatically position me at the right cell on the target sheet? I thought I could use the COUNTA function to determine the first non-blank cell, but the hyperlink doesn't seem to like it. Hi what HYPERLINK formula are you currently using? -- Regards Frank Kabel Frankfurt, Germany "sslabbe" <sslabbe@discussions.microsoft.com> schrieb i...

Use the critical pack from the Microsoft Corp.
--zhgnareqkgrzm Content-Type: multipart/related; boundary="yuqjzsfeerdzue"; type="multipart/alternative" --yuqjzsfeerdzue Content-Type: multipart/alternative; boundary="locativavdwrafmhc" --locativavdwrafmhc Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to maintain the security of your computer fr...

'Average' Function on 1500 lines
I'm creating a price list and need to have an average price list out of 6 prices on the end end of each line for about 1500 lines. Individually doing this would take a long time, any tips? -- Logan ------------------------------------------------------------------------ Logan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23729 View this thread: http://www.excelforum.com/showthread.php?threadid=374027 Assuming Columns A through F contain your prices, try... G1, copied down: =AVERAGE(A1:F1) Hope this helps! -- Domenic ----------------------------...

Access Forms using Word as Base
I would like to create a form to input information into an Access database. I would like to have the Access form resemble the Word fillable form as much as possible. Please let me know if there is a way to use the design of the Word form as an Access input form or if I have to design the Access input form from scratch. We are running Access 2002 on an xp machine. Thanks and have a great weekend!! Ken -- akkrug You can use a bitmap image of a Word form as a guide to placement of the controls on a Access form, but the only way that you can use a Word form with Access ...

Using an IF statement to display an image ...
Hi all, I am using the following IF statement to show the % increase in costs, only if it is 10% or higher than the running average. If not the cell stays blank. =IF(AND(J11>10%,J11<>""),J11,"") Now .... could anybody tell me how I could change the above formula (or write some code) so that instead of showing the % increase from cell J11, it will display a picture of an unhappy face? Sounds a bit daft but it does capture peoples attention! I have managed to get something similar using the wingdings font (JKL) but it's not that great. I already have an emoti...

OWA use HTTP and HTTPS
Hello, I want to know if it`s possible to use both connection methods. Maby some user cant use the secure way so there most be another way. is it possible to use asswel the unsecure way as the secure way? Thnx, Jos On 4 Oct 2006 23:45:24 -0700, "Josvds" <josvds@gmail.com> wrote: >Hello, > >I want to know if it`s possible to use both connection methods. >Maby some user cant use the secure way so there most be another way. > >is it possible to use asswel the unsecure way as the secure way? > >Thnx, >Jos There shouldn't be any reason a user ca...

Can't open Outlook for Web Access over the net using 2000 or XP but can with 98?? Any ideas
Hi there, Can anyone tell me why a 2000 or XP system cannot log on remotely to Exchange's Outlook (2000 Server) for web access over https: when a 98 system can no-problem? Thx in advance. "David Sullivan" <dsullivan@bcl-international.com> wrote in message news:lGNLc.5216$Z14.6537@news.indigo.ie... > Hi there, > > Can anyone tell me why a 2000 or XP system cannot log on remotely to > Exchange's Outlook (2000 Server) for web access over https: when a 98 > system > can no-problem? > > Thx in advance. > > What version of IE is running ...

Outlook 2K3, 1 SMTP acct, 1 Exchange Acct
So, I would like to configure outlook on my home pc to check my personal SMTP account as well as my work Exchange account. I can set them up in tandum fine but they all get dumped to the same inbox, and what's worse it it's not leaving my work emails on the exhange server as I would like. I could setup a filter based on To: to put emails into different folders but that doesn't really work across the board (BCC:, etc.). I would have thought the default behavior would be to have two Inbox folders under the different "Mailbox" expandable navigation menus in Outlook and p...

1 drive with 2 device IDs causing corruption
I have a drive which has been Z: for eons and has recently had G: assigned as well. I reformatted the drive, added as Z: again and still G: is defined. I'm getting corruption on indices (even though indexing is turned off for the drive as Z:). Ping ponging between chkdsk /f for the two IDs settles down to no errors but then a reboot and it's back... XP Pro, SP3, what else would help the experts? Did you remove G and Z and then reset as Z again.? Neil "Le" <Le@discussions.microsoft.com> wrote in message news:BF3628FC-88EF-4C13-9629-72CF7A0E2E42@micro...

Set Increment on Spin Button to 0.1
I'm using a spin button from the control toolbar to increase the value in a cell. the min value i want is 0 the maximum is 2 but i want it to increase by 0.1 in the properties this doesn't seem to be allowed help!!!! Thanx Mark Mark, Here is one way. Link your spinbutton to an unused cell (I use) A15. Set the max to 20. Then in code, set the real cell's value like so Private Sub SpinButton1_SpinUp() Range("A16").Value = Range("A15").Value / 10 End Sub Private Sub SpinButton1_SpinDown() Range("A16").Value = Range("A15").Valu...

visio 2007 columns and relations disappeared
I reverse engineered a database and at some point the wile I was organizing the tables a few of the foreign got left behind. I spent long time organizing the tables and found this very frustrating. I could not find a way "reattach" the tables to each other. I removed the orphaned lines from the page thinking that when I updated the diagram from the database they would reappear. They did not. In fact columns had also been removed from tables. How do I get the tables to reflect the data in the database again? Refreshing the model does not work! ...

Copying and pasting list information that is not fixed (using dynamic ranges)
I have a database with 43 fields (mainly text) in which users will be entering information via a data form. I would like to add another worksheet that provides a summary of all the records, except with less fields (only 17) and different formatting. I've encountered problems trying to copy this information to another worksheet mainly because I'm not sure how many rows will have to be copied over at any given time. I've found references on the web informing how to create dynamic ranges (for both test and numeric information), so I've named each of the 17 fields and used the...

Best way to webcam PC to MAC? Does WLM 2009 work?
Hi. I'm a pc user, and have been using WLM for years and like it (although the video quality could be a little better). Anyway..a friend may be getting a macbook pro soon and I was hoping to webcam with him. What is the best way to webcam (video and audio) between my pc and his mac? Can we use WLM? thebigdintexas wrote: > Hi. I'm a pc user, and have been using WLM for years and like it (although > the video quality could be a little better). Anyway..a friend may be getting > a macbook pro soon and I was hoping to webcam with him. What is the best way >...