Excel - Formula or Macro to fill a cell based on a

问题:

In Excel, I am trying to make a cell based of the values contained in two other cells.

I need Cells X and Y to have data based on Cells L and #, like so....

     X     Y      L       1     2     3     4     5     6

A    6     1      1      6;1   6;1   7;1   7;2   7;2   8;1
B    7     2      4      6;1   6;1   7;1   7;2   7;2   8;1

So row A, has columns X and Y filled based of the values in the number columns. The specific number needed is what is filled in in column L.

I am not sure the best way to phrase this question. If my example doesn't make sense, I can try to clarify or provide more examples. I have no idea if this can be done with fancy formulas or with a VBA macro or two. I am an excel noob.

回答1:

If I've understood your question correctly you can do this with a combination of Left/Right, Index and search.

In my example images, the user inputs their value in column D, and then columns B and C use the formulea

=LEFT(INDEX($F2:$K2,1,$D2),(SEARCH(";",INDEX($F2:$K2,1,$D2))-1))

=RIGHT(INDEX($F2:$K2,1,$D2),(SEARCH(";",INDEX($F2:$K2,1,$D2))-1))

respectively

Here, the Index function returns the correct column to look at (i.e. the value chosen by the user, the Search function finds the position of the semi-colon, and the left/right functions return the values either side of the semi-colon.