Finding a number in a table?
 

................A........B.....C....D.....E.....F. ....G..................H..................I....... .......J


1.......................40...38...36...34...32...3 0......ENTER ROW
WITH.....................36
2.............115.....88...84...79...75...70...66. .....ENTER # PLANTS/
10' ROW.......76
3.............110.....84...80...76...72...67...63. .....POP PER
ACRE........................... ?
4.............105.....80...76...72...68...64...60
5.............100.....77...73...69...65...61...57
6...............95.....73...69...65...62...58...55


OK I have a table and I want to find the answer that is in the A
column. In row 1 is the different planter spacings for planting
soybeans 40 inches to 30 inches. Below those numbers are a table that
is how many plants are in 10 feet of row.
The numbers in the A column are the population per acre of plants.

I know and have entered (36) the row width in J1, I also know and have
entered (76) the number of plants in J2, I don't know how to write the
formula to give me the answer in J3 that would be (A3=110).
This is a pretty big table I am working with, the above is just a
sample.

Any help on this would be great. I am not good at explaining on paper.
Thank you
JoeBed


--
JoeBed
------------------------------------------------------------------------
JoeBed's Profile: http://www.excelforum.com/member.php...o&userid=21368
View this thread: http://www.excelforum.com/showthread...hreadid=376378

JoeBed,

What logic leads you to pick 110 as the answer? What does 110 mean?

HTH,
Bernie
MS Excel MVP


"JoeBed" wrote in
message ...


................A........B.....C....D.....E.....F. ....G..................H...
................I..............J


1.......................40...38...36...34...32...3 0......ENTER ROW
WITH.....................36
2.............115.....88...84...79...75...70...66. .....ENTER # PLANTS/
10' ROW.......76
3.............110.....84...80...76...72...67...63. .....POP PER
ACRE........................... ?
4.............105.....80...76...72...68...64...60
5.............100.....77...73...69...65...61...57
6...............95.....73...69...65...62...58...55


OK I have a table and I want to find the answer that is in the A
column. In row 1 is the different planter spacings for planting
soybeans 40 inches to 30 inches. Below those numbers are a table that
is how many plants are in 10 feet of row.
The numbers in the A column are the population per acre of plants.

I know and have entered (36) the row width in J1, I also know and have
entered (76) the number of plants in J2, I don't know how to write the
formula to give me the answer in J3 that would be (A3=110).
This is a pretty big table I am working with, the above is just a
sample.

Any help on this would be great. I am not good at explaining on paper.
Thank you
JoeBed


--
JoeBed
------------------------------------------------------------------------
JoeBed's Profile:
http://www.excelforum.com/member.php...o&userid=21368
View this thread: http://www.excelforum.com/showthread...hreadid=376378

try
=index(A1:A6,match(j2,offset(A1,match(J1,A1:I1,0)-1,0,7,1)

"JoeBed" wrote:


................A........B.....C....D.....E.....F. ....G..................H..................I....... .......J


1.......................40...38...36...34...32...3 0......ENTER ROW
WITH.....................36
2.............115.....88...84...79...75...70...66. .....ENTER # PLANTS/
10' ROW.......76
3.............110.....84...80...76...72...67...63. .....POP PER
ACRE........................... ?
4.............105.....80...76...72...68...64...60
5.............100.....77...73...69...65...61...57
6...............95.....73...69...65...62...58...55


OK I have a table and I want to find the answer that is in the A
column. In row 1 is the different planter spacings for planting
soybeans 40 inches to 30 inches. Below those numbers are a table that
is how many plants are in 10 feet of row.
The numbers in the A column are the population per acre of plants.

I know and have entered (36) the row width in J1, I also know and have
entered (76) the number of plants in J2, I don't know how to write the
formula to give me the answer in J3 that would be (A3=110).
This is a pretty big table I am working with, the above is just a
sample.

Any help on this would be great. I am not good at explaining on paper.
Thank you
JoeBed


--
JoeBed
------------------------------------------------------------------------
JoeBed's Profile: http://www.excelforum.com/member.php...o&userid=21368
View this thread: http://www.excelforum.com/showthread...hreadid=376378

The table on the left column is the populations. So If I was to just
look at the table and I know the row width is 30 inches I would look
down the column below 30 inches and when I find the correct plants per
ten feet (76) I just look left and the left column has the population
(110). Does that make any sense to you?

Joe


--
JoeBed
------------------------------------------------------------------------
JoeBed's Profile: http://www.excelforum.com/member.php...o&userid=21368
View this thread: http://www.excelforum.com/showthread...hreadid=376378

The way you explained your process is a little bit confusing. However, I
seem to understand as I have a similar application with what you want to
do here.

Try this formula ...

=INDEX($A$2:$A$6,MATCH(J2,OFFSET($A$1,MATCH(J1,$B$ 1:$G$1,0),5,1),0))

and hope this is the one that you need.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=376378

Thanks to BenjieLop and bj,
You both seem to have the same thing in mind. but I tried both of them
and can not get them to work. # N/A comes up. I dug a little deeper and
in the offset area volitale comes up as the answer. I am at a loss.
Thanks Joe


--
JoeBed
------------------------------------------------------------------------
JoeBed's Profile: http://www.excelforum.com/member.php...o&userid=21368
View this thread: http://www.excelforum.com/showthread...hreadid=376378

I am simply wondering why the formula does not work for you. I have
tested it (using your tabulated data) and it works.

Let me ask you ... how many rows do you have in Column A? Also, how
many columns do you have? Do you have columns only up to G?

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=376378

BenjieLop
Column A has the Populations per acre A2:A6 (115 thru 95). A1 has
nothing in it.
Row 1 has the row with in B1:G1 (40 thru 30)
=INDEX($A$2:$A$6,MATCH(J2,OFFSET($A$1,MATCH(J1,$B$ 1:$G$1,0),5,1),0))
This is what I have in my sample. Is it differant from yours?
Thanks for trying
Joe


--
JoeBed
------------------------------------------------------------------------
JoeBed's Profile: http://www.excelforum.com/member.php...o&userid=21368
View this thread: http://www.excelforum.com/showthread...hreadid=376378

JoeBed Wrote:
BenjieLop
Column A has the Populations per acre A2:A6 (115 thru 95). A1 has
nothing in it.
Row 1 has the row with in B1:G1 (40 thru 30)
=INDEX($A$2:$A$6,MATCH(J2,OFFSET($A$1,MATCH(J1,$B$ 1:$G$1,0),5,1),0))
This is what I have in my sample. Is it differant from yours?
Thanks for trying
Joe

Joe,

There is a missing argument in the formula that I gave you.


=INDEX($A$2:$A$6,MATCH(J2,OFFSET($A$1,*1*,MATCH(J1 ,$B$1:$G$1,0),5,1),0))

The missing argument is in Bold RED in the above formula. I am sorry
about the typo in the original formula that I sent. With this
correction, it should be working for you now.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=376378

BenjieLop,
It works :) !!!!
Thank you for helping me. This forum is great for us dummies. I think I
can expand it into my spreadsheet now.
JoeBed


--
JoeBed
------------------------------------------------------------------------
JoeBed's Profile: http://www.excelforum.com/member.php...o&userid=21368
View this thread: http://www.excelforum.com/showthread...hreadid=376378

All's well that ends well, Joe ... glad to be of help.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=376378