Configuring proper syntax for multiple-worksheet and criteria using INDEX-MATCH, and others
Hello,
I’m fairly new to learning advance excel functions. I’m trying to determine what the best approach may be for the following.
I think using INDEX and MATCH combination will resolve my issue but looking for assistance on how to apply the syntax properly across multiple worksheets to get a column to automatically populate a numerical value in worksheet 1 from a select table of numerical values in worksheet 2. This would be after matching multiple criteria from three columns merged into one column on worksheet 1 and adding a numerical value to each in the return value from worksheet 2 back to worksheet 1.
Example. Worksheet 1 col A has one of five locations, col B one of five program names, col C one of five course names.
Only if Column A to C match correctly will data from table in worksheet 2 pull and auto-populate col D in worksheet 1, and add a numerical value under target head count for the given course under a given program at a given location, as already stated.
I want to be able to have the correct numerical value from the table in worksheet 2 to be selected and populated at a designated cell for each row of data in worksheet 1. As described all three variables are concatenated or merged into one column, col D.
Essentially I need to update worksheet 1 periodically and want to be able to use col D to match updated data with table ranges pre-populated from worksheet 2 by using the drag and click feature to auto-populate formulas with new updated data in worksht 1. The objective is to ensure updated rows will match-up to their corresponding target values from worksheet 2 to track the target value in one column and calculate percent-complete on a second column in worksheet 1.
My worksheet is attached to show what I’m attempting to do.
I can clarify as needed. Thanks for any help you can provide. I apologize if this has already been posted addressing each aspect of this request.
|