Index Match Formula

[nsmjc]

I am trying to generate an Index Match Formula to create a small list from a rather large Spread Sheet

In 'Officer Info"! Sheet, I have created a Data Validation List in cell B3

Bellow that I want to display the Case Number, Force Was, Officer Responded with Force, on this sheet if their name appears in Sheet 'Force Responses'! and to correspond to the correct information with the correct line.

Their names may not be entered in the same format so I am trying to use the Match($B$3&"*") function but I cannot get it to work.

This is the formula I am trying to work with but not luck.

=INDEX('Force Responses'!F2:f,MATCH('Officer Info'!B3&"*",'Force Responses'!KA2:SY))

Any Thought?

Thank you for any assistance.

I have hidden a lot of columns so it is easier to see what I am working with.

[JBeaucaire]

Although formulas may work for this, as your worksheet data grows I would expect the performance of said worksheet to possibly get slower and slower.

So on the off chance you'd like a VBA (macro) solution, I've installed a macro into your worksheet. It is triggered by you making a change to cell B3, and it will search the data sheet and pull in the list of officers, and it does it reasonably quickly.

Give it a try.

[nsmjc]

Thank you very much I did not even think of the possible growth on the spreadsheet and the slowing it may have. The VBA works great!