Excel Cell Manipulation

Thanks:  0
Likes:  0

1. ## Excel Cell Manipulation

I am looking to be able to have more than one number in a cell and create a range for a formula to identify that cell.

Example: Cell "B2" will have the numbers 12 and 13 so that when formula is directed to identify the number 12 it will identify that cell.

I may even have more than two numbers like 12 to 14 but I can't seem to get a formula to identify that cell when more than one number is in it????

Can this be done?

Thank you mightly Excell Wizards!!

2. ## Re: Excell Cell Manipulation

I'm not clear on what you want..

"Cell "B2" will have the numbers 12 and 13" means what?
- Cell has 1213 - Cell has 12 13 - Cell has either 12 or 13?

"when formula is directed to identify the number 12 it will identify that cell"
- Does this mean you are trying to "search" for the number 12?

3. ## Re: Excel Cell Manipulation

out of curiosity...what formula are you using (or trying to use) now

....and also, why do you have multiple numbers in a cell (eg: 12,13,14,15 etc) in a one cell? what are you trying to accomplish?

4. ## Re: Excell Cell Manipulation

Hello,

Sorry for not being clear. I am creating a template to score a standardized test that I use in my profession. What I have is some cells just have a standardized single number and I can get my formula to recognize those cells. The problem arises when the standardized results have two or more numbers in a cell. When I was refering to the numbers 12 and 13 (or more numbers) I am saying that any single cell could have those numbers or more but when my formula is looking up lets say the number 12 but I have both 12 and 13 in the same cell, I get an #NA result not the actually number or 12 or 13 etc.

I hope this is more clear, the bottom line is that my formula needs to identify specific numbers in the various cells but most of the cells have single numbers but the cells that have multi numbers result in #NA due to the fact that more than one number is in the cell and I can't get it to identify one of the numbers in the cell or the range of the numbers (I tried 12:13 and 12-13 etc.).

5. ## Re: Excel Cell Manipulation

i'm still a bit confused. Below is how I'm interpreting your posts:

******** ******************** src="www.interq.or.jp http:>*********>
http://www.interq.or.jp/sun/puremis/colo/popup.js">*********>
 Microsoft Excel - Book1 ___Running: 12.0 : OS =
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 D2D3D4 =

A
B
C
D
1
2
12 13 14 15 16 17 18 13Found it
3
18 19 20 21 22 23 24 15No match
4
84 85 95 86 82 11 13 95Found it
 Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

6. ## Re: Excell Cell Manipulation

Your recent post was helpful but I am not sure how to enter it into my formula so I am going to attach it. I am also having trouble responding to your last response.

=IF(AND(\$B\$1>=4,\$B\$1<=4.03),VLOOKUP(\$C\$3,\$J\$3:\$GB\$37,175,FALSE),(IF(AND(\$B\$1>=4.04,\$B\$1<=4.07),(VLOOKUP(\$C\$3,\$J\$3:\$GB\$37,175,FALSE)))))

I have b1 refering to an age range of data. Then vlookup is looking up the data column that has the numbers that I have mentioned to you and transfers the correct number to a specified cell. The column below refers to column "J" and you can see the numbers 38 and 41, those are the numbers I need my formula to understand that in that cell if the number 38 or 39 or 40 or 41 means that it should take that cell and reference it to column GB which is the second column below and return that number. In my example, any number from 38 to 41 should return the number 35. I hope am making myself more clear??

 Female Ages 4.0 - 4.03 Scale Scores Corresponding To Subtest Point Scores Subtest 1: Fine Motor Precision 38 41 37 36 35 34 33 30-32 29 28 25-27 23-24 22 21 20 19 18 17 16 15 13-14 11 12 10 8-9 7 5-6 3-4 2 1 0

 Scale Score 35 34 33 32 31 30 29 28 27 26 25 24 23 22 21 20 19 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1

7. ## Re: Excell Cell Manipulation

jonnyg

Welcome to the MrExcel board!

I'm a little cofused about your two columns - they seem to be different lengths and one has gaps while the other does not. However, I think this should point you in the right direction (at least if I have understood correctly).

1. You need to reverse the order of your data in column J (that is, from smallest to largest).

2. Also in col J, you need just the lowest value in each range. I have put some of your old col J values in col I to show what I mean.

3. Instead of col GB, I have used col K to make my screen shot simple. I also didn't know how the numbers matched up but you should get the idea.

4. Formula in N2 copied down.

Also, since your VLOOKUP table is very large (cols J:GB) you could be slowing your sheet down a bit. Any change in data in J3:GB37 will cause your formulas referring to that range to recalculate. You could improve that by using an INDEX/MATCH construct as I have shown in col O. This way, only changes to the two relevant columns will cause the formula to recalculate.

Post back if you need more help with modifying my suggestion to your layout.

VLOOKUP

 I J K L M N O 1 Col J value Col K result Col K result 2 2 3 3 3 0 0 1 4 4 4 4 1 1 2 40 29 29 5 2 2 3 6 3-4 3 4 7 5-6 5 5 8 7 7 6 9 8-9 8 7 10 10 10 8 11 11 9 12 13 10 13 15 11 14 16 12 15 17 13 16 18 14 17 19 15 18 20 16 19 21 17 20 22 18 21 23 19 22 25 20 23 28 21 24 29 22 25 30 23 26 33 24 27 34 25 28 35 26 29 36 27 30 37 28 31 38-41 38 29

 Cell Formula N2 =VLOOKUP(M2,\$J\$3:\$K\$31,2,1) O2 =INDEX(K:K,MATCH(M2,J:J,1))

Excel tables to the web >> Excel Jeanie HTML 4

8. ## Re: Excel Cell Manipulation

Okay, I am getting more confused. I really appreciate all of your help. The reason I don't want to have to reverse the columns is because I want the spreadsheet to duplicate the test that I am using for ease of undertanding where my data is placed.

I know that I have a large spread sheet but the timing is not an issue. I get a proper response quickly enough. I also want to clarify that when there are blank cells it is because the test does not have data in those cells. The test will take a "Point score" number (the correct responses they give out of a total) and calculate that "Point score" with their age to look up a column from "J" to "GA" and obtains the number in that is referenced to the cell in "GB".

The only issue I am having with this formula is that due to the test itself, they have some cells with multiple numbers. I am really just trying to figure out how do you get the formula (my (if(and.....(vlookup(....) that i have created to read a cell that has multiple numbers to identify that cell.

Example was for the cell that has the numbers "38 to 41" (which is J3 in my sample columns). If I identify a child that is age 4 and they get a "point score" of 38, how can I get the cell to be identified so that it will locate the number "35" in column "GB" that is associated with "J3".

I hope that I am not being more confusing than I need to be but I really and HOPEFULLY am looking for a simple solution. I just can't get the excell spreadsheet to identify a cell that has more than one number, my formula will then reply #N/A if there is more than one number. If i only had one number "38" (only) I would retrieve the "35" from "GB".

Thank you all!!

9. ## Re: Excel Cell Manipulation

Let's deal with a smaller sample data set to make it easier to post (suggest installing Excel jeanie ).

VLOOKUP (1)

 G H I J K L GA GB 1 Col J value Col GT result 2 31 3 38-41 35 4 37 34 5 36 33 6 35 32 7 34 31 8 33 30 9 30-32 29 10 29 28 11 25-28 27 12

Excel tables to the web >> Excel Jeanie HTML 4

Suppose we have this data in J:GB and we want to look up a value of 31. Now as I understand it, you would expect a result of 29. However, you are asking how to get Excel to recoginise that 31 belongs to cell J9 when 31 does not appear anywhere in J9.

On that basis it would appear you are actually looking for some sort of 'approximate' match in column J. Any match/lookup functions that I know, when looking for an approximate match, require the data to be in ascending order.

Your VLOOKUP formula was using 'FALSE' as the final argument, which means you are looking for an exact match. With an exact match lookup the data does not need to be in ascending order, but there does need to be an exact match (pretty logical really). So, if you want the data in descending order, would it be feasible to list all the possible values in col J like this? Then the VLOOKUP (excat match) would work.

VLOOKUP (2)

 G H I J K L GA GB 1 Col J value Col GT result 2 31 29 3 41 35 4 40 35 5 39 35 6 38 35 7 37 34 8 36 33 9 35 32 10 34 31 11 33 30 12 32 29 13 31 29 14 30 29 15 29 28 16 28 27 17 27 27 18 26 27 19 25 27 20

 Cell Formula H2 =VLOOKUP(G2,J3:GB19,175,FALSE)

Excel tables to the web >> Excel Jeanie HTML 4

If you don't want to do this either, then I can't see a formula solution for you and I think you would need to turn to a vba solution.

10. ## Re: Excel Cell Manipulation

Originally Posted by Peter_SSs
Let's deal with a smaller sample data set to make it easier to post (suggest installing Excel jeanie ).

VLOOKUP (1)

 G H I J K L GA GB 1 Col J value Col GT result 2 31 3 38-41 35 4 37 34 5 36 33 6 35 32 7 34 31 8 33 30 9 30-32 29 10 29 28 11 25-28 27 12

Excel tables to the web >> Excel Jeanie HTML 4

Suppose we have this data in J:GB and we want to look up a value of 31. Now as I understand it, you would expect a result of 29. However, you are asking how to get Excel to recoginise that 31 belongs to cell J9 when 31 does not appear anywhere in J9.

On that basis it would appear you are actually looking for some sort of 'approximate' match in column J. Any match/lookup functions that I know, when looking for an approximate match, require the data to be in ascending order.

Your VLOOKUP formula was using 'FALSE' as the final argument, which means you are looking for an exact match. With an exact match lookup the data does not need to be in ascending order, but there does need to be an exact match (pretty logical really). So, if you want the data in descending order, would it be feasible to list all the possible values in col J like this? Then the VLOOKUP (excat match) would work.

VLOOKUP (2)

 G H I J K L GA GB 1 Col J value Col GT result 2 31 29 3 41 35 4 40 35 5 39 35 6 38 35 7 37 34 8 36 33 9 35 32 10 34 31 11 33 30 12 32 29 13 31 29 14 30 29 15 29 28 16 28 27 17 27 27 18 26 27 19 25 27 20

 Cell Formula H2 =VLOOKUP(G2,J3:GB19,175,FALSE)

Excel tables to the web >> Excel Jeanie HTML 4

If you don't want to do this either, then I can't see a formula solution for you and I think you would need to turn to a vba solution.

Okay, I am on the same page as you and finally understand what you are saying. The problem is that I can't change the column data due to the fact that I will have various data results that are spread out across all the columns I mentioned (j to GB will have data all over the scale). I guess what I am trying to do can't be done, what is this VBA solution?

So sad, don't know why a computer can't just recognize that a cell has a range to look into? But I guess they aren't as smart as they should be. Just curious, would my problem work in Windows Vista? The only problem with that is that I have Vista at home but my school program doesn't and therefore, wouldn't be compatible.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•