Finding a number in a table?

**JoeBed** · 06-03-2005, 01:21 PM

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

1.......................40...38...36...34...32...30......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

Bernie Deitrick · 06-03-2005, 03:05 PM

JoeBed,

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

HTH,
Bernie
MS Excel MVP

"JoeBed" <[email protected]> wrote in
message news:[email protected]...
>
>
................A........B.....C....D.....E.....F.....G..................H...
................I..............J
>
>
> 1.......................40...38...36...34...32...30......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** · 06-03-2005, 03:58 PM

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

bj · 06-03-2005, 04:05 PM

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...30......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
>
>

**BenjieLop** · 06-03-2005, 04:13 PM

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.

**JoeBed** · 06-04-2005, 10:14 AM

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

**BenjieLop** · 06-04-2005, 10:55 AM

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.

**JoeBed** · 06-04-2005, 11:37 AM

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

**BenjieLop** · 06-04-2005, 11:55 AM

Originally Posted by JoeBed

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.