Log In
Register
Home
New Member
April 11, 2012
Offline
Hi,
I'm looking for help with formulas to automate filling in information into a table in one worksheet into another worksheet using perhaps INDEX MATCH. In my first worksheet, I have appointments between two different groups of people at assigned tables. It's similar to a hosted buyer tradeshow. Group A are people who are stationary at the tables and Group B are people that move around. In the separate worksheet, I have a list of all of the people in Group B in a column and all of the times available for appointments listed across the top. I would like to figure out how to find the people in Group
B from the list, find the times they are meeting and then list who they are meeting with in each time slot along with the table #. And if there is no appointment for them in that timeslot, I would like it to be blank. Is it possible to do this with INDEX MATCH or are there some other functions that could do this? I have attached the spreadsheet that I need help with.
Thanks,
Tonya
See Answer
VIP
Trusted Members
December 7, 2016
Offline
Hello Tonya,
I have put together three different approaches, see the attached file.
In short, you can use VLOOKUP function if you create a helper column. In Appt List-Members worksheet I added such helper column as column C. What is showing in that column is Start Time & Member combined, by doing so you can use VLOOKUP to look for multiple criterias.
If you don't want to use a helper column, you can of course use INDEX and MATCH, but as you look for multiple criterias you need then create this as an array formula (Ctrl + Shift + Enter).
Both these approaches works, but you will not get any dynamic update in case you add more start times, you have to add more fields manually in your layout. Also, as you see if you look in the cells showing the table number, the formula is not the same in columns showing table number as for the columns showing customer name. If you don't intend to extend and add more columns (start time and tables) then this is only a problem when typing in the formulas.
The third approach is to use a PivotTable to show the data, it will not be exactly as you constructed it, but almost. And if you create a table for your source data and have this table as data source for the PivotTable it gives you the advantage that you can add more members and start times and the PivotTable will update dynamically when refreshing the PivotTable. And no need to create any troublesome formulas that risks to stop working due to errors when updating the sheet.
Take a look and see what suits you the best.
Br,
Anders
Answers Post
New Member
April 11, 2012
Offline
Hello Anders,
Thank you very much for your help. I really appreciate it! This will save me a lot of time and will keep me from making mistakes in transcribing the information.
Best regards,
Tonya B
1 Guest(s)
