Excel - Matching Two Tables Based On Criteria

[Kpowz]

Hi there,

I need help with the following:

I am trying to link the output of a table based on if set criteria are met, for example, if the function is listed as business process and the individual has grade A against the record then it returns T1 against the record. This is from a different worksheet. And am guessing it involves an index match but for the life of me can't get it to work.

Please see below for the Grade Table tab into the main data table please see the attached sample excel for reference.

Many thanks

[ChemistB]

Okay, I assume you are trying to fill in column F (and G?) in the Incumbent Data sheet. Can you explain some of your results. If I look at line 14 (Engineering Grade A), I would expect either T1 (if you're getting your results from the engineering column of Internal Grades or M1 if you're getting your results from Col C. How do you end up with M2? Please explain how you are pulling data.

Also, what are you doing with cell P9 in Internal grades?

[Kpowz]

Hi ChemistB,

Thanks for responding, Yes it is column F and G that I am looking to populate and the results are based on mangers being identified from the first table and this was a simple vlookup I conducted after I filtered out the managers. As I wasn't sure if it was also possible. You can ignore the cell in P9 as this was just an error and was trying to do an index match. It should state "P1".

Thanks

[Glenn Kennedy]

I am hugely confused. You expect a formula in Incumbent data F&G. Please explain how you got the results that are entered there? I can't see the logic at all.

[ChemistB]

So that still doesn't explain how you ended up with M2 when the code in Internal Grades for Grade A is M1? Are we looking at the engineering column and finding the first value (that would be T4 in cell H6) and then looking across to Col C to find M2? (even though that line (line 6) says Grade D)?

[Kpowz]

Hi both,

Ignore the information that is entered as that should I have been removed before I inputted the file.

Yes ChemistB that is what I am trying to achieve if an individual sits in engineering in column C and have Grade A in Column B, then I want that to return the values from the Internal Grade sheet in H9 to report back into column F in the incumbent data sheet.

There are two tables in the internal data sheet as I want to add an extra column into the incumbent data so it flags if they are manager with a yes/no and then if yes it does the above lookup in the first table starting B2 in the the Internal Grading sheet, if no then it looks at the other table which starts at F3. Both processes using the same as above to return values into column F and column G if applicable.

Thanks

[ChemistB]

Okay, here's what I think you are asking
In F24 of Incumbent Data for example...
If there is a value in Logistics on the Grade A row (there is, "W1") then return the associated M value from Col C ("M1"). If there is no value under Logistics in that row, return nothing.

Is that what you want?

[Kpowz]

Yes that sounds about right, sorry for the misunderstanding.

I am looking for it to return the value "W1" from that table based on it being in logistics by looking at the function above as they all have different values dependent on the set grading for example having grade A, and if there is a manager status of yes then refer to to col c value based on just the grade column. I have attached an updated sheet which should hopefully help.

[ChemistB]

See if this is what you are looking for. In G9 copied down

Please Login or Register  to view this content.

In H9 copied down

Please Login or Register  to view this content.

[Kpowz]

This is perfect! Can't thank you enough! Exactly what I was looking for!