Finding a number in a table?

[JoeBed]

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


1.......................40...38...36...34...32...30......ENTER RO
WITH.....................36
2.............115.....88...84...79...75...70...66......ENTER # PLANTS
10' ROW.......76
3.............110.....84...80...76...72...67...63......POP PE
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
column. In row 1 is the different planter spacings for plantin
soybeans 40 inches to 30 inches. Below those numbers are a table tha
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 hav
entered (76) the number of plants in J2, I don't know how to write th
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
sample.

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

 

[Bernie Deitrick]

JoeBed,

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

HTH,
Bernie
MS Excel MVP

 

[Guest]

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

 

[JoeBed]

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

 

[BenjieLop]

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]

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

 

[BenjieLop]

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]

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]

BenjieLop
Column A has the Populations per acre A2:A6 (115 thru 95). A1 ha
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 sorr
about the typo in the original formula that I sent. With thi
correction, it should be working for you now.

Regards

 

[JoeBed]

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

 

[BenjieLop]

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

Regards.