shortning if fromula

[shamsu203]

Hi
I have this formula whcih is copied from a13 to z13. how can i shorten the same . tired xlookup but did not work
=IF(AND($B$2=$AB$4,$D$4=AD4),$AF$12/2,IF(AND($B$2=$AB$5,$D$4=$AD$4),$AF$5/2,IF(AND($B$2=$AB$6,$D$4=$AD$4),$AF$6/2,IF(AND($B$2=$AB$7,$D$4=$AD$4),$AF$7/2,IF(AND($B$2=$AB$8,$D$4=$AD$4),$AF$8/2,IF(AND($B$2=$AB$9,$D$4=$AD$4),$AF$9/2,IF(AND($B$2=$AB$10,$D$4=$AD$4),$AF$10/2,IF(AND($B$2=$AB$11,$D$4=$AD$4),$AF$11/2,0))))))))
where b2 and ab4 to ab11 are names ,d4 and ad4 are dates in the format apr-2008 apr 2030,,af 5 to af 12 are amounts.I would appreciate any help provided . I tried xlookup but no success
Shamsu

 

[KRice]

Does your spreadsheet structure make it possible to copy the content of cell AF12 to AF4? If so, then you would have two well-aligned arrays AF4:AF11 and AB4:AB11 that could be used like this:

=IF($D$4=$AD$4,INDEX($AF$4:$AF$11,MATCH($B$2,$AB$4:$AB$11,0))/2,0)

Also, is there any chance that B2 will not match anything in AB4:AB11? If so, this formula will return an error, so an error trap would be needed to return 0 in that case.

If this is not possible, then what about copying AB4 to AB12...would that be possible? If so, then the ranges in the above formula could be modified slightly.

 

[shamsu203]

hi
Tks for ur prompt reply. the answer to ur questions are
contents of af12 can be copied to AF 4 which is blank at the moment. B2 will always match ab4:ab11.

 

[KRice]

Good, then try copying AF12 to AF4, and then see if the following gives the expected result.

=IF($D$4=$AD$4,INDEX($AF$4:$AF$11,MATCH($B$2,$AB$4:$AB$11,0))/2,0)

 

[shamsu203]

HI
tks this formula works fine as expected

 

[KRice]

That's great...glad to help.