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.

Attached Files

Example Model.xlsx (18.7 KB, 8 views)

I am not sure what it is that you want, but if it is to extract the number at the right of the string in Track Table!D2 you can use:

=LOOKUP(9^9,-RIGHT(D2;ROW($1:$100)))

Thanks Xor,

I'd like to incorporate that if I can. I was puttering around a little more with what I started and think I established a very basic version attached.

In the first worksheet labeled Track I would need to somehow make a dynamic range so that when I add new updated rows the table will expand and formulas in col F would follow suit to match corresponding cell values and data range. Hope this makes sense.

Attached Files

Prototype Model.xlsx (17.7 KB, 9 views)

I am still in doubt, but take a look at the attached (also see Name Manager) and let me know if I have misunderstood.

Attached Files

Prototype_2.xlsx (18.8 KB, 7 views)

Not sure what part you may find doubtful. Thanks for the suggestion. I found a working configuration. I can share if interested. Bet I''ll be back when I encounter new stuff to learn. Cheers!