Formula Help Please

[Keith - NRCS]

I hope there is an easy answer to my formula problem so I will try to explain
the best that I can. I am trying to relate ranges of soil types to their
corresponding dollar amounts.

Here is the formula I have working so far
=IF(OR(C11=Q34,C11=Q35,C11=Q36,C11=Q37,C11=Q38,C11=Q39,C11=Q40,C11=Q41,C11=Q42,C11=Q43,C11=Q44,C11=Q45,C11=Q46,C11=Q47,C11=Q48,C11=Q49,C11=Q50,C11=Q51,C11=Q52,C11=Q53,C11=Q54,C11=Q55,C11=Q56),R5,0)

Basically this formula takes into account what is entered into cell C11
(soil type) and it relates a corresponding dollar amount to it. The problem I
am having is that I have 4 more ranges of cells and each range of those cells
has a different corresponding dollar amount.

I am on the right track because the formula above works as long as I enter a
soil type from the first range of soil types. Basically I need to add on to
the formula above.

Also if there is a way to shorten the formula and have it recognize a range
of cells...I know you can do that but I was getting an error when I tried to
highlight the entire range so I had to click each cell individually to get it
to work. Any tips or help would be greatly appreciated.

 

[Jacob Skaria]

Try MATCH() function..You can have more criterias and change the range

=IF(ISNA(MATCH(C11,Q34:Q56,0)),0,R5)

If this post helps click Yes

 

[T. Valko]

Let's start by shortening your formula:

=IF(COUNTIF(Q34:Q56,C11),R5,0)

 

[Jacob Skaria]

If you have soil type in Q34:Q56 and corresponding dollar in R34:R56 try the
below formula which will return the formula

=IF(ISNA(MATCH(C11,Q34:Q56,0)),0,INDEX(R34:R56,MATCH(C11,Q34:Q56,0)))

If this post helps click Yes

 

[Keith - NRCS]

This may better explain my data. The numbers on the right are the soil types.
They are mostly numbers but some are numbers and letters.
I have a worksheet where you can enter the soil type (numbers and letters)
and then in a column next to that it will automatically bring in
the dollar amount.

Does that help explain it better?

SRR Soil Map Unit Symbols
$119 35 247 362 525 1016 1030 1075 1080 1084 1091 1095 1834
27B 327B 39A 39B 41A 41B 611C 920C2 920D2 960F L205A

$129 255 269 392 517 539 1228 1213C 920B 921C2 960D2 L13A L200A

$141 112 114 386 1092 1833 1229B 944B 945C2 L163A L84A

$154 86 313 956 978 1213B L107A L184A L185B L34A

$168 109 113 336 414 887B 945B L83A

$181 118 239 102B 106B 1204B 1207B 1901B 238B 94B L85A

 

[T. Valko]

Hmmm...

You'd be better off if you could create *one* table instead of 6.

Like this:

Type...Price
35...119
247...119
362...119
255...129
269...129
392...129
112...141
114...141
386...141
etc
etc
etc

Then, if C11 = user entered soil type:

=SUMIF(A2:A50,C11,B2:B50)

 

[Keith - NRCS]

Maybe I am over thinking this. Will it be easier to do if I organize my data
like so:

35 119
86 154
109 168
112 141
113 168
114 141
118 181
239 181
247 119
255 129
269 129
313 154
336 168
362 119
386 141
392 129

....etc. Soil type on the left and dollar amount on the right.

 

[T. Valko]

Will it be easier...

Yes! See my reply in the other branch of this thread.

 

[Jacob Skaria]

Biff, is there a change in your email id from what is mentioned in the
profile..

If this post helps click Yes

 

[Keith - NRCS]

Thanks Biff!

Now one more question. When I paste the formula into the cell it works fine.
But how do I drag it down or copy it to the rest of the column without the
cell numbers ascending automatically?

 

[Jacob Skaria]

Use absolute referencing. Below are the different reference styles.
A1 Relative referencing. Both column and row will change if you copy or drag
the formula.
$A1 The column reference is fixed and will not change
A$1 The row reference is fixed and will not change.
$A$1 Column and row reference are fixed.

If this post helps click Yes

 

[T. Valko]

If you mean (e-mail address removed), that's a bogus address. Spam that
address all you want!

 

[Jacob Skaria]

To you query yesterday 'about using alias name' I have send a mail which
bounced back..and hence checked..

If this post helps click Yes

 

[Keith - NRCS]

OK...I ran into another snag with it. It works great for the soil types that
start with a number but it enters a "0" in the cell if it is a soil type that
starts with a number. Any ideas?

 

[T. Valko]

Just disregard that alias name question. I see you're posting from a
different location so that answered my question.

 

[Keith - NRCS]

Sorry, typo. It enters a "0" if it is a soil type that starts with OR has a
letter in it.

 

[T. Valko]

Which formula did you end up using?

 

[Keith - NRCS]

This one:

=LOOKUP(C11,$A$33:$A$104,$B$33:$B$104)

It worked great...and I actually stumbled onto it myself in the Help menu.
Thanks for the help!

 

[T. Valko]

Are you sure that formula works correctly? It requries the table data be
sorted in ascending order and is better suited for data that is all the same
data type.