special lookup

[Guest]

Hello,

Is it possible to use a vlookup to reference a range of data, but not have
the exact value of the text you are looking up?

For instance: if I am looking up data in A1:A500. I know the lookup_value
has at least the text "Total". However, it turns out that in the range
A1:A500 the value I am looking for has the title "This is the Total"

As I began the question, I would like to use a vlookup to pull in a value
from this range, so I guess my basic question is how would I modify the
lookup_value or the table_array portion of the vlookup formula to make this
work?

Let me know if you need more info.

Thanks,

 

[Guest]

=VLOOKUP("*Total",A1:B500,2,0)

 

[Guest]

How can I do essentially the same thing using the Sumproduct formula, and if
the word total appears anywhere in the word? Such as the word total is in a
cell as "Total People" or "The Total of All" or "The Total"

When I combine the vlookup formula suggested below into the sumproduct, the
result only brings back one of the items and not all the items with the word
Total.

Thanks,