Formula to generate a value

[jeffamore]

I have a workbook with two sheets. One called "County Info" and one called "Values". On the Values sheet is a table similar to below.

XLS screenshot.JPG

On the County Info Sheet, the corresponding columns for this information is:
Type = EC
Zip Code = AF
Acreage = EH

I need a formula in a column on the County Info sheet that will look at the values in Type (EC), Zip Code (AF), and Acreage (EH), return the appropriate corresponding Value from the table on the Values sheet, and then multiply that value times the Acreage (EH). For example:

If Type is Commercial, Zip Code is 12345, and Acreage is 2, the result of the formula would be 40000
If Type is Residential, Zip Code is 67890, and Acreage is 10, the result of the formula would be 10000

This formula would need to be copied down thousands of rows. Thanks in advance!

[etaf]

it would be better to upload a sample sheet with expected results - SEE the yellow banner at top of thread

In your example , you have 2 commercial with 2 entries for the same Zipcode , BUT with different acres and value

A lookup index/match should be able to do that

you have the same zip code and type, not sure how you know which one to use - i suspect a sample sheet with explanation of example results would help a lot

[jeffamore]

Thanks.... I've attached a quick sample with the results I would expect. The value is based on the matching type, zip code and acreage. Between 0 and 5 acres would pull the Value in the row with the 0 in Acreage. Anything >= 5 acres would pull the value in the row with 5 in the acreage. Hopefully that makes sense.

[etaf]

this will return the Value
=INDEX(Value!$D$1:$D$200,MATCH(B2&C2&IF(D2<5,0,5),Value!$A$1:$A$200&Value!$B$1:$B$200&Value!$C$1:C200,0)) - Which I have put in column H, not use in result - BUT just to show the result of the lookup for you to check

Then multiply by the Acres
Which i have put in E
=INDEX(Value!$D$1:$D$200,MATCH(B2&C2&IF(D2<5,0,5),Value!$A$1:$A$200&Value!$B$1:$B$200&Value!$C$1:C200,0))*D2

[jeffamore]

That looks correct to me! I'm assuming the '200' in your formula is just to define the number of rows? So if I had 1000 rows, that would be 1000. Is that right?

[etaf]

correct, sorry, i should have mentioned that

[jeffamore]

Awesome... I'm going to give it a try. Thank you!

[etaf]

you are welcome