Indexing and Matching

I am trying to use the index and the match function.

Assume field E27 and F 27 with the values as shown below

=INDEX({"1","2","3","4","5","6","7","8","9","10"},MATCH(E27,{"Remote","Unlikely","Possible","Likely","Almost Certain"},FALSE)*MATCH(F27,{"Insignificant","Minor","Moderate","Significant","Severe"},FALSE))

If i click on "Remote" and the values in F27, it displays correctly 1,2,3,4,5

But, if i click the next one "Unlikely" and values in F27, it displays 2,4,6,8,10 - i would like it to display 6,7,8,9,10

Can you please let me know what should be modified so i get what i want?

Attached is the file as requested.

Please change Column A and see how the Column C values changes from 1 through 5 for every change in Column B but when i change Column A now to the second value in the drop down, and change column B, Column C does not show me 6 through 10 but instead 2,4,6,8,10 - i understand why is this but want to display 6 through 10, in other words whatever i want in the index array.

Hi, I dont need the numbers displayed like 6 through 10. Please see my workbook and the example

No, it does not. I dont want the cells to display 6 through 10 hard coded.

If you open my spreadsheet and select "Remote" and any combination from column B, column C displays you values from 1 through 5 which is good but if you select the next drop down from Column A and values from Column B, now the result is not 6 through 10 (I want to display the values in place of 6 through 10 and not the numbers 6 through 10) it displays result 2,4,6,8,10 (this is because if you imagine a 2 by 2 matrix, when you select column A and B, it multiplies A which is 1 with column B which is 1, 2, 3,4 and 5 hence the results 1,2,3,4,and 5 -

Now if you select the select one in column A, the value is 2 now and multipled with column B , gives 2,4,6,8,10 but i dont want that instead i want it to display what i need which is value 6,7,8,9,10 because the contents in 6,7,8,9,10 are not 1,2,3,4,5.

Here is the formula

=INDEX({"1","2","3","4","5","6","7","8","9","10"},MATCH(E27,{"Remote","Unlikely","Possible","Likely","Almost Certain"},FALSE)*MATCH(F27,{"Insignificant","Minor","Moderate","Significant","Severe"},FALSE))

Please open my spreadsheet and correlate with my explanation.

@ xl

Originally Posted by Ace_XL

Not sure I understand your requirement. Dosen't the solution provided give you desired results?

You need to look at post #5. They did not want the numbers "displayed like 6 through 10"

Thanks so much. I got the desired results. You are amazing.

Happy New Year!!!!

I have two columns, Column A has numbers and Column B has text (A through E for the sake of simplicity) - i would have to display the reply in Column C as numbers by multiplying Column A and B - How do i convert Column B into numbers and then multiply with Column A values?

Kinda, but its all part of the above indexing and matching question.