Lookup with Variables

[Guest]

This formula needs to be in Col AR

I have tried =LOOKUP(AND(F4,"??? Total"),F4:F720,AI4:AI720)

I need to find a cell in Col F that contains the value from the same row
followed by the word "Total" and returns the value from Col AI in the same
row.
(ie. F4 = 108, F7 = 108 Total, AI7 = 7.05)

The problem is that I have multiple cells with "??? Total" where "???" is a
three digit number relating to the group above it.

Can anyone help?

~Robert

 

[Biff]

Try this:

=INDEX(AI4:AI720,MATCH(F4&" Total",F4:F720,0))

Biff

 

[Guest]

Maybe something like this:

With your posted scenario
and
A1: 108

This formula returns the "108 Total" value (assuming that "108 Total"
appears only once in the range)
B1: =SUMIF(F4:F720,A1&" Total",AI4:AI720)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP