Efficient worksheet change vba code

[antmol89]

Hi all

Does anybody no a way to perform the actions of the following code more efficiently / with less lines of code. As is, I will need to run similar versions of it over 500 times (Will this cause a speed issue?).

Each version of the code will look for a different word (72 in total) in cell "I24" (highlighted below). The code will then perform an Index and Match on different sheets (72 in total) based on the previous word identified.

My second question is whether I can put two words in the IF equation (i.e., IF(I24=UTG and I25=Four,Index....)?

Private Sub Worksheet_Change(ByVal Target As Range)


 [COLOR=#000000] If Target.Address = "$I$25" Then
        Range("N16").Value = [IF(I24="UTG o",INDEX(UTG!B3:B171,MATCH(I23,UTG!A3:A171,0)),"")]
  End If


  If Target.Address = "$I$25" Then
        Range("N18").Value = [IF(I24="UTG o",INDEX(UTG!C3:C171,MATCH(I23,UTG!A3:A171,0)),"")]
  End If


  If Target.Address = "$I$25" Then
        Range("N20").Value = [IF(I24="UTG o",INDEX(UTG!D3:D171,MATCH(I23,UTG!A3:A171,0)),"")]
  End If


  If Target.Address = "$I$25" Then
        Range("N22").Value = [IF(I24="UTG o",INDEX(UTG!E3:E171,MATCH(I23,UTG!A3:A171,0)),"")]
  End If


End Sub[/COLOR]

Really appreciate anybody who can help on this one!

Cheers!

 

[mole999]

IF(I24=UTG and I25=Four would usually be written like IF(AND(I24="UTG",I25="Four" etc