Looking up a table then returning a number based on criteria

[MarkoGreenio]

Hi all,

I work with WFM Verint and the inhouse reports are not user friendly with Excel and reconciliation.

I have attached a document and what I would like to return in cell P8 are the remaining LUK Annual Leave hours for the person identified in cell O8 looking at the table from rows B - L

I have tried numerous formulas (an example in M8 on the attached FYI) and tried with this.

Administrators - please advise if I have forgotton anything or you requre the title descriptive changing.

Thank you for your help

Mark

[Pete_UK]

It would help if you had the Employees' names on each row. As you are not using column A, you could put this formula in A9:

=IF(ISNUMBER(SEARCH("Employee",B9)),TRIM(RIGHT(B9,LEN(B9)-SEARCH("Employee",B9)-8)),A8)

Copy this down to the bottom of your data.

Then you can use this formula in cell P8:

=SUMIFS(K:K,A:A,"*"&TRIM(RIGHT(O8,LEN(O8)-10)),B:B,"LUK Annual Leave")

and copy that down as required. I don't see much point in having 'Organization: Bet Lynch' listed in column O - surely you just want the employees listed?

Hope this helps.

Pete

[MarkoGreenio]

It would help if you had the Employees' names on each row. As you are not using column A, you could put this formula in A9:

=IF(ISNUMBER(SEARCH("Employee",B9)),TRIM(RIGHT(B9,LEN(B9)-SEARCH("Employee",B9)-8)),A8)

Copy this down to the bottom of your data.

Then you can use this formula in cell P8:

=SUMIFS(K:K,A:A,"*"&TRIM(RIGHT(O8,LEN(O8)-10)),B:B,"LUK Annual Leave")

and copy that down as required. I don't see much point in having 'Organization: Bet Lynch' listed in column O - surely you just want the employees listed?

Hope this helps.

Pete

Fantastic Pete, thank you

[Pete_UK]

You're welcome.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[MarkoGreenio]

Absolutely I will, I will scan down the other guys who have helped me in the past.