Excel 2016 Vlookup issue with dropdown

Guys

basically I'm putting together a risk matrix in excel. the user selects "Probability" and "Impact" values from 2 dropdowns on the Risks& Issues sheet and I need to have the RAG value calculated based on the combination of P & I.

The drop down values for P & I are taken from lists on a second sheet called Matrix beside each is a value between 1 & 5. these values are used to calculate a "Risk Total" or RAG rating

User selects values from dropdowns on "Risk & Issues Probability column" and also Risk & Issues Impact Column". "Risk & Issues RAG Column then looks to the Matrix sheet and caries out a simple multiplication based on values in column C.

As you can see if I put the VLookup on the Matrix sheet the calculation in G2 works as expected. but as soon as I try to calculate that RAG rating on the RISKS & Issues sheet its not calculating. I think the issue is around the col_Index_Num in my formula, I cant seem to get it to look to the MATRIX sheet.

Hope that explanation makes sense.



any help would be appreciated.

You've changed the ranges in the VLOOKUP in H4 on the Risks&Issues sheet. Try this instead:

=IFERROR(VLOOKUP($F4,Matrix!$B$2:$C$6,2,0)*VLOOKUP($G4,Matrix!$B$7:$C$11,2,0),"not match")

Hope this helps.

Pete

Pete

cheers that seems to have worked and I can copy it all the way down. All I need to do now is add a Conditional format and I'm sorted. much appreciated. I've been at that for over an hour LOL

Glad to help.

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, since you are relatively new to the forum, 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