Hello. I am green to Excel, but I am trying to create a worksheet for my
own use. I have written a 2 column table of calculated numbers. Now I
have another number generated that I wish to compare to the first
column in the table. The generated number will not be an exact match.
If the generated number is greater than a first column number but less
than the next first column number, I wish to return the associated 2nd
column number. Is this possible? There will be over 1000 numbers to
compare. Is there one single string of code that will work for all
comparisons? I can write this with if statements, but each cell is
unique and I don't think I will live long enough to write 1000 cell.
Thanks


--
spxer
------------------------------------------------------------------------
spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025
View this thread: http://www.excelforum.com/showthread...hreadid=567497

You can write an IF function and then drag it all accross the column. If you
have data in columns A and B, the generated number in column C, and you want
to put the formula in column D, you can write something like this in cell D1:
=IF(AND(C1A1,C1<A2),B1,"No")
And then select that cell and double click on the small square on the bottom
right corner of the cell border. Excell will fill the formula on the column
for all the cells that have something on column C, and the references will
change accordingly.

Hope this helps,
Miguel.

"spxer" wrote:


Hello. I am green to Excel, but I am trying to create a worksheet for my
own use. I have written a 2 column table of calculated numbers. Now I
have another number generated that I wish to compare to the first
column in the table. The generated number will not be an exact match.
If the generated number is greater than a first column number but less
than the next first column number, I wish to return the associated 2nd
column number. Is this possible? There will be over 1000 numbers to
compare. Is there one single string of code that will work for all
comparisons? I can write this with if statements, but each cell is
unique and I don't think I will live long enough to write 1000 cell.
Thanks


--
spxer
------------------------------------------------------------------------
spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025
View this thread: http://www.excelforum.com/showthread...hreadid=567497

Two problems; 1 there are over 50 rows of cells to be compared to and 2
when I copy and paste the code, cell labels that I don't want to change
do change, which renders the code useless


--
spxer
------------------------------------------------------------------------
spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025
View this thread: http://www.excelforum.com/showthread...hreadid=567497

What do you mean by 50 rows of cells to be compared? Is that the generated
number is compared against other 50, or just that you have 50 rows of data in
total? If you can be more specific on what you want to achieve, and what is
the layout of your data, we may help you better.
Regarding labels, you can fix rows and column adding a $ sign in front of
the part of the reference you want to fix. For example, $A1 will keep
looking on the A column, and A$1 won't change to other row but 1.

Miguel.

"spxer" wrote:


Two problems; 1 there are over 50 rows of cells to be compared to and 2
when I copy and paste the code, cell labels that I don't want to change
do change, which renders the code useless


--
spxer
------------------------------------------------------------------------
spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025
View this thread: http://www.excelforum.com/showthread...hreadid=567497

I have over 1000 cells to compare to over 50 values. lets say A1:A50 is
values of 100 through 10,045, but not evenly divided (can't make this
easy or anyone could do it, even me.) b1:b50 is 1 through 50. Now I
have a coulmn of 1000 numbers to associate with the table a,b. Find the
number in column "a" that is less than the number in question but the
next number down is greater. then return the coresponding number in
column b. IE; say the number to compare is 222. in table a,b we find a
number series .....a5=201, a6=220, a7=235..... a5 or 220 is the correct
asociation. and b6 is returned.
Miguel, What is your reference to $ called? I could not find this in
help.
Thanks again and all for any help.


--
spxer
------------------------------------------------------------------------
spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025
View this thread: http://www.excelforum.com/showthread...hreadid=567497

Sorry correction: a6 is the correct association... " IE; say the
number to compare is 222. in table a,b we find a number series
.....a5=201, a6=220, a7=235..... a6 or 220 is the correct asociation.
and b6 is returned."


--
spxer
------------------------------------------------------------------------
spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025
View this thread: http://www.excelforum.com/showthread...hreadid=567497

This is a good scenario for VLOOKUP, using your layout of A1:B50, and
assuming that the A column is sorted (it looks like that in your post), the
formula to use can be:
=VLOOKUP(222,$A$1:$B$50,2)
You can change the first number to a cell reference. For example, if you
have your 1000 numbers in cells D1:D1000, you can put this formula in cell E1
=VLOOKUP(D1,$A$1:$B$50,2)
And drag it all over the column E.

Regarding the $, check the help for "cell and range references", it explains
the meaning of the sign better than I can do.

Miguel.

"spxer" wrote:


I have over 1000 cells to compare to over 50 values. lets say A1:A50 is
values of 100 through 10,045, but not evenly divided (can't make this
easy or anyone could do it, even me.) b1:b50 is 1 through 50. Now I
have a coulmn of 1000 numbers to associate with the table a,b. Find the
number in column "a" that is less than the number in question but the
next number down is greater. then return the coresponding number in
column b. IE; say the number to compare is 222. in table a,b we find a
number series .....a5=201, a6=220, a7=235..... a5 or 220 is the correct
asociation. and b6 is returned.
Miguel, What is your reference to $ called? I could not find this in
help.
Thanks again and all for any help.


--
spxer
------------------------------------------------------------------------
spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025
View this thread: http://www.excelforum.com/showthread...hreadid=567497

Miguel, VLOOKUP was just what I needed. It seems to be working. Thank
you. You are great!


--
spxer
------------------------------------------------------------------------
spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025
View this thread: http://www.excelforum.com/showthread...hreadid=567497