yungintern
New Member
- Joined
- Jul 20, 2017
- Messages
- 11
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.
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.
