Vlookup with base data tothe left

[andyb16]

Hi All,

I have data with subtotals as shown below:

Invoice Amount Invoice Month Amount Paid
10 Jan-2011 11
20 Jan-2011 21
30 Jan-2011 31
60 Jan-2011 Total 63
40 Feb-2011 41
50 Feb-2011 51
60 Feb-2011 61
150 Feb-2011 Total 153
210 Grand Total 216


I want to write a formula that looks up for the column that contains the string "Jan-2011 Total"(the subtotals/Invoice Month column) and gives the result of the value to the left i.e the subtotal of the Invoice Amount (for Jan its 60). I was able to do this when data was to the right of the Invoice Month column. But am struggling to do the same when data is to the left. Please assist.

Thanks

 

[Jonmo1]

Index/Match..

If the Dates are in column B, and the subtotal is column A

=INDEX(A:A,MATCH(somedate,B:B,0))

Hope that helps.

 

[Ron Coderre]

If the data is in Columns A:C

This regular formula returns the Col_A value that corresponds to the "Jan-2011 Total" cell

=INDEX(A1:A100,MATCH("Jan-2011 Total",B1:B100,0))

Is that something you can work with?

 

[andyb16]

Works perfectly!! Thanks jonmo1

 

[andyb16]

Thanks Ron. This works splendidly as well.