SOLVED

Copper Contributor

Hi Everyone!

I'm trying to do something I have never tried before.  Normally, I can figure most things out using online forums, but I'm not even sure how to phrase a search for this.  So, here it goes.

I have a list of people, each with 5 options associated with them.  Looks sort of like this (slashes indicated separation of colums):

Person 1/Option 1/Option 2/Option 3/Option 4/Option 5/Column 6/Column 7

Person 2/Option 1/Option 2/Option 3/Option 4/Option 5/Column 6/Column 7

And so on.  What I want to do is create another column (this would be Column 6) where I can type in Option 1 or Option 3 or whatever option I choose and have the cell in Column 7 extract the data that correlates.

Anyone know of a way to do this?

Please let me know if it doesn't make any sense.

Thank you!

Anton Dagner

6 Replies

Can you please format some excel example?
The way you mentioned can confuses us.
I'm sure it can help with some ideas I have in mind, but I would like your assistance on provide some sample of how the data is distributed.

Here is an example of what I'm trying to do:

@Juliano-Petrukio Thanks for replying.  Just joined this forum today and have no idea how this all works, but I replied to my original message.  Here is a screen shot of what I have set up.  What I am trying to do is put in the cells under the heading "Bonus Level" is a number from 1 through 5 and have it pull the amounts from the corresponding columns into the "Bonus Amount" field.  This way, I can change the Bonus Level numbers and have the Bonus Amounts auto populate.

best response confirmed by AntonDagner (Copper Contributor)
Solution

It will return the value based on your choice in column J (considering numbers 1 to 5) as per your example;

``=INDEX(E3:I3,1,J3)``

Perfect. Thank you!

In this case two intermediate function one for the row and another for the column. The row function is needed to get the index for the row and the match function is needed to get the index for the column; These are in addition to the index function already suggested. Pay attention that row is always changing going downward and the option selected would determine the column to horizontally lookup the bonus.
1 best response

Accepted Solutions
best response confirmed by AntonDagner (Copper Contributor)
Solution

It will return the value based on your choice in column J (considering numbers 1 to 5) as per your example;

``=INDEX(E3:I3,1,J3)``