Trouble with Text Types and Formula?

[jeffamore]

I have a workbook with two sheets.

Sheet1 has columns A-IQ and the columns that are pertinent are below:
Column AI - Zip Code
Column EK - Acreage
Column IP - Offer Price

Sheet2 has columns A-C:
Column A - Zip Code
Column B - Acreage Range
Column C - Market Price Per Acre

I need to calculate the correct Offer Price in Column IP on Sheet1 using the Market Price Per Acre in Column C on Sheet2 for the matching Zip Code in Column AI on Sheet1 and Acreage in Column EK on Sheet1 that matches the acreage range for that zip code in Column B on Sheet 2 multiplying all of that times 34.7%. I'm trying to use the formula below.

=INDEX(Sheet2!$C$1:$C$57,MATCH(ek2,IF(Sheet2!$A$1:$A$57=Sheet1!AI2,Sheet2!$B$1:$B$57,"")),1)*EK2*347

These are the table values for zip code 81639

81639	0​	$220,000.00​
81639	0.25​	$115,000.00​

Example:
AI3 = 81639
EK3 = .26
IP3 should equal $10,375.30 ($115000*.26*.347)

Right now the result in column IP I'm getting is #N/A and I believe it's due to the zip code column types not matching, but I did try to make them match using the Text to Columns feature.

Any help with this would be greatly appreciated!

 

[DanteAmor]

Check if this is what you need:

Dante Amor
	AI																																																																																																										EK																																																																																																													IP
3	81639																																																																																																										0.26																																																																																																													$10,375.30
Sheet1

Cell Formulas
Range	Formula
IP3	IP3	=INDEX(Sheet2!$C$1:$C$57,LARGE((Sheet2!$A$1:$A$57=AI3)*(Sheet2!$B$1:$B$57<=EK3)*ROW(Sheet2!$C$1:$C$57),1))*EK3*34.7%
Press CTRL+SHIFT+ENTER to enter array formulas.

_________

varios 21jun2021.xlsm
	A	B	C
1	Zip Code	Acreage Range	Market Price Per Acre
2	81639	0	$ 220,000.00
3	81639	0.25	$ 115,000.00
Sheet2

 

[jeffamore]

Check if this is what you need:

Dante Amor
	AI																																																																																																										EK																																																																																																													IP
3	81639																																																																																																										0.26																																																																																																													$10,375.30
Sheet1

Cell Formulas
Range	Formula
IP3	IP3	=INDEX(Sheet2!$C$1:$C$57,LARGE((Sheet2!$A$1:$A$57=AI3)*(Sheet2!$B$1:$B$57<=EK3)*ROW(Sheet2!$C$1:$C$57),1))*EK3*34.7%
Press CTRL+SHIFT+ENTER to enter array formulas.

_________

varios 21jun2021.xlsm
	A	B	C
1	Zip Code	Acreage Range	Market Price Per Acre
2	81639	0	$ 220,000.00
3	81639	0.25	$ 115,000.00
Sheet2

This does work.... thank you! Can I ask WHY it works, though? Does this take care of any data mismatches between the columns (AI on Sheet 1 and A on Sheet 2)? I've had this issue before and I always struggle to get it to work for some reason, so I'd love to be able to have it resolved once and for all.

 

[DanteAmor]

What the formula does is, it goes to sheet2 and gets all the zip codes equal to 81639, on sheet2 you have several equal zip codes, then you must get all of them, then compare the Acreage of each zip code against 0.26 but none is the same, the closest is 0.25, that's why in the formula it is "<=", but 0 and 0.25 , both are less than 0.26, then we must obtain the largest of 0 and 0.25, for that is the LARGE formula, then the largest is 0.25, then we already have the zip code and the Acreage, then we obtain the row number of that combination; and finally, with Index we obtain the price. And for that the array formula is necessary.

 

[jeffamore]

What the formula does is, it goes to sheet2 and gets all the zip codes equal to 81639, on sheet2 you have several equal zip codes, then you must get all of them, then compare the Acreage of each zip code against 0.26 but none is the same, the closest is 0.25, that's why in the formula it is "<=", but 0 and 0.25 , both are less than 0.26, then we must obtain the largest of 0 and 0.25, for that is the LARGE formula, then the largest is 0.25, then we already have the zip code and the Acreage, then we obtain the row number of that combination; and finally, with Index we obtain the price. And for that the array formula is necessary.

Thank you so much... really appreciate it! Hopefully I won't need to come back and ask a similar question again. Seems like every time I create a list like this I have this problem and each time someone provides a new resolution.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.