Index Match / Look Up - same data point in different rows

[ExcelUser18]

Excuse the vague title. I have a giant spreadsheet in a similar format below. This is a rent roll which I need to be able to search for the Tenant Name but also search for the specific billing type (for example "Rent"). For Tenant 1, it would be very easy to search for Tenant 1 (cell B3) and billing type "Rent" (cell F3) incorporated in a lookup/index-match formula. The issue is when the Billing Type "Rent" does not match up with the Tenant name (example: Tenant 2 and "Rent" do not match up). Additionally, sorting it would cause confusion because then all the other billing types dedicated to that specific tenant would be thrown out of whack.

Is there anyway to search for "Rent" and apply it the appropriate "Tenant"?

Please note the Letters on top and the numbers on the left indicate the location in teh spreadsheet. I tried pasting it from my excel template but if there are any questions re: my inquiry, please feel free to sound off!



[TABLE="width: 591"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Tenant[/TD]
[TD]Header 1[/TD]
[TD]Header 2[/TD]
[TD]Header 3[/TD]
[TD]Billing Type[/TD]
[TD]Header 5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tenant 1[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Rent[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Utilities[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Misc[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Pet[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Parking[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Tenant 2[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Utilities[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Parking[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Pet[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Misc[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Rent[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Tenant 3[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Rent[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Utilities[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Misc[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Parking[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Misc[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Tenant 4[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Pet[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Rent[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Misc[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Utilities[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Parking[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]Tenant 5[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Utilities[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Rent[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Misc[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Pet[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Parking[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]Tenant 6[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Parking[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Rent[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Misc[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Pet[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]Utilities[/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]

 

[anglais428]

Is there any reason why you cannot just duplicate the Tenant name in Column B? E.g. cells B3:B7 would be 'Tenant 1'?

 

[ExcelUser18]

Is there any reason why you cannot just duplicate the Tenant name in Column B? E.g. cells B3:B7 would be 'Tenant 1'?

The spreadsheet is thousands of rows long with several hundred tenants. Also, this is 1 spreadsheet out of almost a dozen. Copying and pasting the tenant name, over and over, would be mind numbingly long.

Also in my spreadsheet, I used 5 different billing types but in reality, they vary from 2 types per tenant up to 7 types per tenant. Even if I could regiment a copy and paste every few lines, it still wouldn't work out because of the varying rows/tenant. If there is a different formula for that (a totally different request) that would be great.

 

[anglais428]

There is a quick way to insert the tenant text automatically:
1) Highlight the Tenant range
2) CTRL G - special, blanks, OK
3) This will place the active cell as B4. Enter "=" and then press the upward arrow (effectively cell B4 will say '=B3')
4) Press control + Enter (not just enter). This will insert the appropriate Tenant name for each cell in Column B

 

[ExcelUser18]

There is a quick way to insert the tenant text automatically:
1) Highlight the Tenant range
2) CTRL G - special, blanks, OK
3) This will place the active cell as B4. Enter "=" and then press the upward arrow (effectively cell B4 will say '=B3')
4) Press control + Enter (not just enter). This will insert the appropriate Tenant name for each cell in Column B

.....Unbelievable. Bravo to you sir, you fixed an issue that has caused me heartache for quite some time. Granted this is the easier fix (as I have a few more steps to modify the spreadsheet) but fantastic. Thanks again for your time and help!