INDEX/MATCH Formula?

Hi, I need help with a formula.  My data looks like this:

Column A	Column B
1	3
2	7
3	5
4	8
5	1
6
7
8
9
10

And I need it to end up like this:

Column A	Column B
1	1
2
3	3
4
5	5
6
7	7
8	8
9
10

So, in other words, the numbers in Column B need to end up in the same
row as their corresponding numbers in Column A.  To do it manually
will take forever as the real data is thousands of rows long.  Can
this be done with an INDEX/MATCH formula of some kind?  I=92ve tried a
few different things on my own but I clearly don=92t know what I=92m
doing.  Any help would be greatly appreciated.
0
fj5380 (5)
7/9/2010 7:15:46 AM
excel 39879 articles. 2 followers. Follow

5 Replies
546 Views

Similar Articles

[PageSpeed] 50

Can you please explain why you have skipped the 2,4,6,9 & 10 values in
B Column?  Any reason behind this?  In which method these numbers are
skipped?

-----------------------
Ms-Exl-Learner
-----------------------


On Jul 9, 12:15=A0pm, F J <fj5...@gmail.com> wrote:
> Hi, I need help with a formula. =A0My data looks like this:
>
> Column A =A0 =A0 =A0 =A0Column B
> 1 =A0 =A0 =A0 3
> 2 =A0 =A0 =A0 7
> 3 =A0 =A0 =A0 5
> 4 =A0 =A0 =A0 8
> 5 =A0 =A0 =A0 1
> 6
> 7
> 8
> 9
> 10
>
> And I need it to end up like this:
>
> Column A =A0 =A0 =A0 =A0Column B
> 1 =A0 =A0 =A0 1
> 2
> 3 =A0 =A0 =A0 3
> 4
> 5 =A0 =A0 =A0 5
> 6
> 7 =A0 =A0 =A0 7
> 8 =A0 =A0 =A0 8
> 9
> 10
>
> So, in other words, the numbers in Column B need to end up in the same
> row as their corresponding numbers in Column A. =A0To do it manually
> will take forever as the real data is thousands of rows long. =A0Can
> this be done with an INDEX/MATCH formula of some kind? =A0I=92ve tried a
> few different things on my own but I clearly don=92t know what I=92m
> doing. =A0Any help would be greatly appreciated.
0
7/9/2010 8:26:41 AM
Hi, the examples I gave just use random numbers.  No numbers are being
skipped.  They just don't exist in Column B.  In reality, the data
looks more like this:

Column A	Column B
3000	7500
5000	6000
1000	5500
8000	3000
2000	2500
2500
5500
7500
7000
6000

And I want it to look like this:
Column A	Column B
3000	3000
5000
1000
8000
2000	2500
2500
5500	5500
7500	7500
7000
6000	6000

I just want to get it so that the numbers in Column B are in the same
row as that same number in Column A.  There are more numbers in Column
A than there are in Column B and not every number will have a match.
Unfortunately, there are thousands of rows, so it would be difficult
to do this manually.




On Jul 9, 4:26=A0am, Ms-Exl-Learner <ms.exl.lear...@gmail.com> wrote:
> Can you please explain why you have skipped the 2,4,6,9 & 10 values in
> B Column? =A0Any reason behind this? =A0In which method these numbers are
> skipped?
>
> -----------------------
> Ms-Exl-Learner
> -----------------------
>
> On Jul 9, 12:15=A0pm, F J <fj5...@gmail.com> wrote:
>
>
>
> > Hi, I need help with a formula. =A0My data looks like this:
>
> > Column A =A0 =A0 =A0 =A0Column B
> > 1 =A0 =A0 =A0 3
> > 2 =A0 =A0 =A0 7
> > 3 =A0 =A0 =A0 5
> > 4 =A0 =A0 =A0 8
> > 5 =A0 =A0 =A0 1
> > 6
> > 7
> > 8
> > 9
> > 10
>
> > And I need it to end up like this:
>
> > Column A =A0 =A0 =A0 =A0Column B
> > 1 =A0 =A0 =A0 1
> > 2
> > 3 =A0 =A0 =A0 3
> > 4
> > 5 =A0 =A0 =A0 5
> > 6
> > 7 =A0 =A0 =A0 7
> > 8 =A0 =A0 =A0 8
> > 9
> > 10
>
> > So, in other words, the numbers in Column B need to end up in the same
> > row as their corresponding numbers in Column A. =A0To do it manually
> > will take forever as the real data is thousands of rows long. =A0Can
> > this be done with an INDEX/MATCH formula of some kind? =A0I=92ve tried =
a
> > few different things on my own but I clearly don=92t know what I=92m
> > doing. =A0Any help would be greatly appreciated.- Hide quoted text -
>
> - Show quoted text -

0
fj5380 (5)
7/9/2010 9:15:29 AM
Hi F J,

Am Fri, 9 Jul 2010 02:15:29 -0700 (PDT) schrieb F J:

> I just want to get it so that the numbers in Column B are in the same
> row as that same number in Column A.  There are more numbers in Column
> A than there are in Column B and not every number will have a match.
> Unfortunately, there are thousands of rows, so it would be difficult
> to do this manually.

in C1:
=IF(COUNTIF(B:B,A1)>=1,A1,"") and fill down.
Select Column C and copy. Select B1 => Edit => Paste Special => Paste
Values


Regards
Claus Busch
-- 
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
0
claus_busch (107)
7/9/2010 9:29:12 AM
Hi, Claus, thank you so much!   I just tried your formula and it
worked great! :)  I really appreciate your help! :)




On Jul 9, 5:29=A0am, Claus Busch <claus_bu...@t-online.de> wrote:
> Hi F J,
>
> Am Fri, 9 Jul 2010 02:15:29 -0700 (PDT) schrieb F J:
>
> > I just want to get it so that the numbers in Column B are in the same
> > row as that same number in Column A. =A0There are more numbers in Colum=
n
> > A than there are in Column B and not every number will have a match.
> > Unfortunately, there are thousands of rows, so it would be difficult
> > to do this manually.
>
> in C1:
> =3DIF(COUNTIF(B:B,A1)>=3D1,A1,"") and fill down.
> Select Column C and copy. Select B1 =3D> Edit =3D> Paste Special =3D> Pas=
te
> Values
>
> Regards
> Claus Busch
> --
> Win XP PRof SP2 / Vista Ultimate SP2
> Office 2003 SP2 /2007 Ultimate SP2

0
fj5380 (5)
7/9/2010 9:42:50 AM
I am using google reader for viewing the post and the post are
reflecting very late in google reader and anyway you got answer for
your query.

-----------------------
Ms-Exl-Learner
-----------------------

On Jul 9, 2:15=A0pm, F J <fj5...@gmail.com> wrote:
> Hi, the examples I gave just use random numbers. =A0No numbers are being
> skipped. =A0They just don't exist in Column B. =A0In reality, the data
> looks more like this:
>
> Column A =A0 =A0 =A0 =A0Column B
> 3000 =A0 =A07500
> 5000 =A0 =A06000
> 1000 =A0 =A05500
> 8000 =A0 =A03000
> 2000 =A0 =A02500
> 2500
> 5500
> 7500
> 7000
> 6000
>
> And I want it to look like this:
> Column A =A0 =A0 =A0 =A0Column B
> 3000 =A0 =A03000
> 5000
> 1000
> 8000
> 2000 =A0 =A02500
> 2500
> 5500 =A0 =A05500
> 7500 =A0 =A07500
> 7000
> 6000 =A0 =A06000
>
> I just want to get it so that the numbers in Column B are in the same
> row as that same number in Column A. =A0There are more numbers in Column
> A than there are in Column B and not every number will have a match.
> Unfortunately, there are thousands of rows, so it would be difficult
> to do this manually.
>
> On Jul 9, 4:26=A0am, Ms-Exl-Learner <ms.exl.lear...@gmail.com> wrote:
>
> > Can you please explain why you have skipped the 2,4,6,9 & 10 values in
> > B Column? =A0Any reason behind this? =A0In which method these numbers a=
re
> > skipped?
>
> > -----------------------
> > Ms-Exl-Learner
> > -----------------------
>
> > On Jul 9, 12:15=A0pm, F J <fj5...@gmail.com> wrote:
>
> > > Hi, I need help with a formula. =A0My data looks like this:
>
> > > Column A =A0 =A0 =A0 =A0Column B
> > > 1 =A0 =A0 =A0 3
> > > 2 =A0 =A0 =A0 7
> > > 3 =A0 =A0 =A0 5
> > > 4 =A0 =A0 =A0 8
> > > 5 =A0 =A0 =A0 1
> > > 6
> > > 7
> > > 8
> > > 9
> > > 10
>
> > > And I need it to end up like this:
>
> > > Column A =A0 =A0 =A0 =A0Column B
> > > 1 =A0 =A0 =A0 1
> > > 2
> > > 3 =A0 =A0 =A0 3
> > > 4
> > > 5 =A0 =A0 =A0 5
> > > 6
> > > 7 =A0 =A0 =A0 7
> > > 8 =A0 =A0 =A0 8
> > > 9
> > > 10
>
> > > So, in other words, the numbers in Column B need to end up in the sam=
e
> > > row as their corresponding numbers in Column A. =A0To do it manually
> > > will take forever as the real data is thousands of rows long. =A0Can
> > > this be done with an INDEX/MATCH formula of some kind? =A0I=92ve trie=
d a
> > > few different things on my own but I clearly don=92t know what I=92m
> > > doing. =A0Any help would be greatly appreciated.- Hide quoted text -
>
> > - Show quoted text -

0
7/9/2010 11:19:28 AM
Reply:

Similar Artilces:

Hash Match best?
I just saw 2 conflicting statements in 2 different posts and am confused on whether Hash Matches are good or bad when optimizing. I saw this earlier today: A clustered index scan means the query is looking at every entry in the index. You should try using a WHERE clause to turn that into an index seek instead. As for the hash match, that's the best inner join type you can get, so if it's slow all you can do is try to reduce the number of records being joined. I just saw this one: As an aside, though, both execution plans use a Hash Match Inner Join. Generally sp...

Entering array formula using VBA
The following is an array formula.... =IF(OR(G26="",VLOOKUP(G26,$G$16:$H$23,2,FALSE)=0),"",MAX(IF($G$16:G26=G26,$H $16:H26)+1)) 1. Is there a way to enter this into the cell (G26) using VBA (as an array)? 2. If so, if the row that this is to be entered is diff to row 26, how does this get entered into say, G35 and the references changed appropriately? Rob Hi Rob, try this Sub rob() Range("F26").FormulaArray = "=IF(OR(G26="""",VLOOKUP(G26,$G$16:$H$23,2,FALSE)=0),"""",MAX(IF($G$16:G26=G 26,$H$16:H26)+1))" ...

Indexing in Word 2003
Our book was created and indexed using Word 2003. Now we added about 25% more content. Is there a simple way to apply the old index to the new content without doing it manually on every word or phrase? Thanks In a word, no. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Baha" <Baha@discussions.microsoft.com> wrote in message news:49DE73B9-9646-4082-AD49-3095125597B0@microsoft.com... > Our book was created and indexed using Word 2003. > Now we added about 25% more content. > Is there ...

Summing days that match a criteria and date
I have a list of referrals which includes date of appointment, status (e.g. urgent, non-urgent etc.), appointment date, waiting time (number of days from appointment received to appointment date). Thanks to help already received I can count the number of referrals received in a month/year that match a particular status. What I would like to do now is find the average waiting times for that month/year. For a start I’ve been trying to sum the waiting time days using variations of the following: SUMPRODUCT(--MONTH(Referrals!$C$4:$C$1200)=MONTH(B$2), --YEAR(Referrals!$C$4:$C$1200...

Index add primary key
There are many indexes on many tables that have indexes on one column, such as name. And these are usually non-unique. If I added the PK to it (which is usually an integer), that would make it unique (Name, PK). Would that be better than the non-unique index that is smaller? I always make mine with the PK but this client has them without in many places. Thanks, Tom In line... "tshad" <tfs@dslextreme.com> wrote in message news:ObIMqmB0KHA.4752@TK2MSFTNGP04.phx.gbl... > There are many indexes on many tables that have indexes on one column, ...

Formula copying from one workbook to another...
Hello Excel community friends. Here's my problem: I trying to copy a formula from a worksheet in one workbook to a worksheet (of the same name and same cell location) in another workbook. Say the formula from workbook1/worksheet1 is =sum(1+1) and the name of workbook1 is "Me" and the name of the worksheet1 is "Sheet1" and located in cell A1. Of course, when you copy the formula from "Me" to another workbook (named, say, "You") and to the same location (A1) and on the same named worksheet, "Sheet1", the preface of the formula is s...

Crystal Reports formula to Access
Any suggestions on how to use this formula in Access? if not(IsNull({MASTER.JMT_DATE})) then "Post Judgment"else if (IsNull({MASTER.JMT_DATE})) and not(IsNull({MASTER.SUIT_DATE})) then "Post Suit, Pre Judgment"else if (IsNull({MASTER.SUIT_DATE})) and (IsNull({MASTER.JMT_DATE})) then "Pre Suit" Here is a guess -- IIF([MASTER].[JMT_DATE] Is Not Null, "Post Judgment", IIF([MASTER].[JMT_DATE] Is Null AND [MASTER].[SUIT_DATE] Is Not Null, "Post Suit, Pre Judgment", IIF([MASTER.SUIT_DATE] Is Null AND [MASTER].[JMT_DATE] Is Null, "...

Copying and adjusting formulas
Hi Firstly thanks for many tips picked up in this ng - I've learnt a lot. Copying and adjusting formulas 26 sheet workbook (Excel 2000) with a sheet for each weeks figures broken down departmentally and totalled for the week. From sheet 2 onwards the sheet has year to date being current weeks figures + previous year to date. This was originally in .123 and as you copied from one sheet to other sheets the formula adjusted the sheet name - in Excel it keeps the formula without adjustment. I find this weird (and very long winded as each sheets formula will need changing). Am I missin...

Formula question -simple for those who know
Hello I'm trying to add a number that was calculated by means of a formula in one column to the sum of a different column. Example: Column A (running balance) Column B (sum) =+e5+c6-d6 =sum(h1:h8) Col A's number is running balance. I copied the formula down so that the next row changes the formula to =+e6=c7-d7 I want that running balance to add to a sum in another column. I can add the first cell to it, but it doesn't change if the running balance changes. I know that is because it's pointing to that single...

overlaying formulas?
I'm not familiar with the proper terms, but I am trying to collect information in a schedule I have created. Is there a way to have three different totals for count only from the same three vertical columns. I will be glad to explain further, if someone can help. I may not be asking in the proper terms. Thanks for any help. Give more specifics, like column letters and row numbers and what you want to count. ************ Anne Troy www.OfficeArticles.com "Charlie" <Charlie@discussions.microsoft.com> wrote in message news:00BDEBCD-CA3E-49E0-B0EC-328A1E4EB74E@microsoft...

Formulas and sorting
I've put formulas in eacg column C cell to get me the percentage of th column B total in each cell of column B. Formula is B1=sum(B1:B10) and this works but I can't sort and retain the proper percentage. Ther must be a way to do this that would allow me to sort and maintain th proper value. Any help -- Message posted from http://www.ExcelForum.com You need to make the reference to B1:B10 absolute. Use: =B1/SUM($B$1:$B$10) HTH Jason Atlanta, GA >-----Original Message----- >I've put formulas in eacg column C cell to get me the percentage of the >column B total in each...

Help with formula 04-20-10
Hi, I hope you can help me with this formula. I have two spreadsheets called the following:- 1) SME main 200410 2) SME PRE accounts CAT1_RCB checked 120310 (2) Coloumn A in both spreadsheets contains a list of numbers (e.g. 1012381211420) and both coloumns contain 6905 rows. I need a formula in cell b1 of spreadsheet 2 that will look at the number in cell a1 of spreadsheet 2 and if that number is in coloumn A of spreadsheet 1 then I want cell b1 of spreadsheet 2 to return a value of Yes if not then I want a value of No Thanls It's easier to set this up with bot...

Error: Value in Formula of wrong data type
I have a worksheet that uses custom formulas on several inputs from that page. I've used it for a while and never had a problem, but made some changes elsewhere in the worksheet (not linked to this part) and now on many of the cells using these formulas, I get a '#VALUE!' error, stating 'Value in Formula of wrong data type.' The data type is the same it has always been, so I don't know why it's not working now (formula just uses if thens to compare different numbers and return values like 0, 1, or 2). What's even more perplexing is that several ...

Count partial matched text string
I have 3 spreadsheets. The first, “Supplies Requests Received” where Column B contains the (duplicated) names of internal offices placing supply requests (e.g., SNF, DGEN, CANM, etc.) and Column G contains each date a request for supplies was received from that office. Column B Column B SNF 1//29/2009 DGEN 4/19/2008 CNMN 2/4/2009 ASEV 12/11/2008 SNF 2/3/2009 ASEV 9/16/2008 DGEN 5/1/2008 The second spreadsheet “Supplies Delivered” where Column A is a link of Column B from the above “Supplies Requests Received spreadsheet and Column G (of Supplies Delivered”) contains the names of ALL ...

Formula bar
Hi All, Is there any way (I hope so) to set the maximal size of the formula bar? I have large formulas and the expanding formula bar is simply hiding half of my sheet and a lot of cells I need to see. Please advise (no, writing smaller formulas is not an option :) Thanks Marton No, it is no adjustable AFAIK. Why is not writing smaller formulae an option, you can put intermediary steps in hidden columns, it also makes debugging easier. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "VilMarci" <dontsend@here.mail> wrote in mes...

Index, Match, Lookup HELP PLEASE!
First I want to say I have read many posts and tried many suggestions on those post before posting my question hoping I wouldn't have to ask a question that has been answered several times already. I am creating an Excel workbook for my business math class. On one page is the actual math problems and on the other two sheets, each one has a table. One table is Present value of $1 and the other is Future Value of a $1. When I try and put a formula in the math problem sheet that is referenced to one of the tables I get #NA or one of the many other error messages. Here is ...

Couple of questions on formulas
If I want a column to look at a column containing a date, and figure th persons age at that time, I am using this formula.: =DATEDIF(H371,A371,"y") However, I would like to modify this so that if no date is given, i column H, it will return "Undisclosed", and if no data has been entere in either column a or h, it shows blank. I know that this is doin wit ,"" but I am not sure where to put it? Can anyone help -- Message posted from http://www.ExcelForum.com Hi try =IF(H371="","Undisclosed",IF(A371="","",DATEDIF(H371,A37...

Formula ?
I got 3 columns in excel. First two is ID & Name, third column is a subset of the 2nd Column. Now, I want the corresponding ID's for my 3rd column (which should be looked up in first 2 columns) in my 4th column. ID,Name,Subset-name,Subset-ID? -- Failing to plan is planning to fail On Sun, 25 Apr 2010 01:24:04 -0700, Joe <Joe@discussions.microsoft.com> wrote: >I got 3 columns in excel. First two is ID & Name, third column is a subset of >the 2nd Column. Now, I want the corresponding ID's for my 3rd column (which >should be looked up in fir...

Copying range format and formulae without data
I want to set up a macro that copies a range to a new range, copying formulae and cell formats, but ignoring raw data existing in the range copied from. In other words I'm expanding a blank copy for a new period. One way would be to name a blank copy in a range elsewhere and copy it in at the cursor, but for various reasons I'd prefer not to do it that way this time. I have made a 2 stage copy procedure, using the "Paste Special" Alt ESF and Alt EST routines, but this process also copies raw data, presumably seeing the data as a label, even although I have formatted the...

Indexing Foreign Keys
Hello, [Across SQL2000 - 2008] Though we are not facing any performance issue with any query, just curious to know whether a non-clust index is necessary for a Foreign key in a OLTP table? I noticed that in our database, some FKs do not have any indexes. I did not yet check the respective query-plan but want to know if its a good practice. tia, AbbA Hi AbbA, The lack of indexes related to foreign key references is a very common problem I see with performance issues in SQL Server. We always go looking for any foreign keys that aren't the left-hand component...

Index / Match / value in a range?
Hi there, I have three colums of data in a worksheet from A1 to C5, example; Column A Column B Column C ABC 5/2/10 02:00 Dog DEF 5/2/10 02:15 Cat ABC 5/2/10 03:47 Mouse GHI 5/2/10 01:20 Bird ABC 5/2/10 00:11 Horse What I need to do in a seperate worksheet in A1 is to look up a value in column A, find its lowest figure in column B and return the corresponding value from column C. Then in A2 vlook up the same value in column A, find the SECOND lowest figure in column B and return the...

Lookup/match function
With these functions they find the highest value which is less than or equal to the lookup value. For example A B 1 30 2 60 3 70 4 80 5 100 If I want the lookup the 'A' value of 66 it will return '2'. I want to lookup the highest value ie anything over 60 return value of 3. Anybody have any ideas ? Simon -- spgprivate ------------------------------------------------------------------------ spgprivate's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35862 View this thread: http://www.excelforum.com/showthread.ph...

Userform button setting variable from formula
I have a userform that contains windows media player. I want to set CommandButton4 to take the value of the formula in cell A2 of the active sheet and set it as the value of variable named 'inset'. I'm finding the VBA code on this much harder to write than I thought it would be. Any ideas please, anyone? Hi Teepee I am a bit confused by your request but I think that it as simple as: Private Sub CommandButton4_Click() inset = Range("A2").Value MsgBox ("The value of cell A2 is: " & inset) End Sub Regards Shau -- Shaun -----------------------------...

Populate a cell if values in cell 1 and cell 2 match cell 3 and 4
I need help with a formula please. I need to search through a column of data and populate a cell if it meets a condition. For example: IF FirstWorksheet!A2 = SecondWorksheet!ColumnAx and FirstWorksheet!B2 = SecondWorksheet!ColumnBx Then populate SecondWorksheet!Cx with value from FirstWorksheet!C2 I have to match up a column of data in one worksheet with data in 2 columns in another worksheet. Help plz. Thanks T- A multi-criteria index n match (array-entered) should work here In Sheet2, Copy n paste this into C2's formula bar, then array-enter the formula by pressing CTRL+...

Image index.
Hello. I want to use imagelist. The nodes on the image list has connected to an itemlist, which I have added some pictures on it. On runtime - everytime I select a specific node, the image is changed to : imageIndex. I cannot see a way to avoid that situation. I want that when I click on node - the image on it won't be changed. How can I do that ? Thanks :) On 05/06/2010 11:52, Mr. X. wrote: > On runtime - everytime I select a specific node, the image is changed to > : imageIndex. > I want that when I click on node - the image on it won't be changed....