CHALLENGE: regarding an Index/Match Formula

[yungintern]

Hi all,

I'm working on a spreadsheet that requires me to look up payment schedules based on a property's geocode between two sheets. The tricky part, however, is that I must match three different types of payment (tax, operating expenses, and rent) for each geo code.

To make things more complicated, the field that I am using to match these three subtypes must correspond to a different code in the other sheet. For example. "Rent" in Sheet2 must match the code 1.1 in Sheet1.

Here is what I have so far, simplified through a dummy spreadsheet but the idea remains the same. In my example Rent=1.1 Operating Expense= 1.2, and Tax=1.3.

{=INDEX(Sheet1!B:L,MATCH(0,(Sheet2!,J:J=C2)*(Sheet2!,B:B=E2),0,),9)}

I think the problem may lie in the red text but honestly idk.

Basically I want the formula to work like this: Find the property based on geocode, determine the type of payment by converting the text from sheet2 to the code on sheet1, and return the correct payment amount.

Thanks for the help.

 

[Marcelo Branco]

Welcome to Mr Excel forum

To increase your chance of getting help you should provide a small data sample (~10 rows) along with expected result(s).

To post a data sample see section B at
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

M.

 

[yungintern]

Sheet1 Payment Code							Geo Code	Payment Amount
1.1								AAAAAA		$100.00
1.2								AAAAAA		$200.00
1.3								AAAAAA		$300.00
					Sheet2 Payment Type Geocode Payment Rent AAAAAA Tax BBBBBB Operating Expenses CCCCCCC <colgroup><col><col span="5"></colgroup><tbody> </tbody>

<colgroup><col span="10"><col></colgroup><tbody>
</tbody>