INDEX / MATCH and HAS / CONTAINS?
I'm determining how long a person has been parked based on the parking lot zone number and the amount of money paid. I have a separate reference sheet that lays out the zone numbers, location names, and all of the possible amounts someone could have paid in this lot - this is the sheet I'm trying to reference.
When I import the data, I have the ParkMobile Zone numbers to determine the correct row on the reference sheet to search for the amount of money they paid.
I want to use my Reference Sheet to match the ParkMobile Zone and Parking Amount columns from my imported sheet to the Reference Sheet and use those matches to obtain the value in Row 1 of my Reference Sheet, which indicates the amount of time the person parked.
I've tried every variation of IF, MATCH, INDEX, FIND, COLLECT, HAS that I can think of, and while I sometimes get a result, it isn't what I'm looking for. Here's what I'd like the formula to do in steps:
- Match the ParkMobile Zone in my import sheet to the ParkMobile Zone on the Reference Sheet to determine the row I need to search the Parking Amount in.
- Match the Parking Amount in my import sheet to the row we've found in the previous step to determine which column the Parking Amount is located in.
- Finally, use the column associated with the cell we've just found in step 2 to find the value of that column in Row 1 of the Reference Sheet.
I've tried helpers as well, thinking that if step 2 above could at least find me the column number that the Parking Amount in question is located on the determined row, I can reference that column number using a VLOOKUP on a second reference sheet that names the column based on the number. I still couldn't quite figure that out.
I need to be able to do one of the following things:
- Have a formula tell me what column number in my Reference Sheet allows me to MATCH the row with the ParkMobile Zone to the column with the Parking Amount. Then, I can use my secondary reference sheet with a helper column.
- find the value in Row 1 of the Reference Sheet corresponding to the previously determined column with the Parking Amount available at the parking lot in the ParkMobile Zone row.
I cannot reverse engineer this formula to save my life. Any thoughts?
Answers
-
something like this should work:
Make sure to correct the references names as needed…
=INDEX({1hr - 5hr 1st row}, 1, MATCH([Parking Amount]@row, INDEX({1hr - 5hr Columns}, MATCH([ParkMobile Zone]@row, {ParkMobile Zone column}], 0)), 0))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!