Lookup based on criteria

[hinklewj]

Hello - column A is a list of customers. Each customer row has a grand total at the end. The title of that column is "Grand Total". On the next tab I am trying to do a lookup formula that will give me each customers grand total. The Grand Total column number will change each month. I'm assuming the answer is an index/match function but I'm struggling with it.

Thanks!

 

[DPChristman]

SORRY DIDN"T REALIZE THIS WAS AN OLD POST UNTIL I REPLIED

You can do a vlookup from one spreadsheet or tab
On the tab where the lookup values reside, create a total column at the end that adds month by month for each row.

On your vlookup reference, the total column becomes your lookup range.

So (example), if column A contains your customer ID numbers, cells B1-M1 would be each month, and column N would be the total for each customer (simple SUM formula).

if you put customer # in cell A2, your vlookup might look something like this, =VLOOKUP(P8,A:N,14,0)

If you are keep a year after year running history, create separate tabs or workbooks for each year, and keep the summary on a new tab or workbook.

 

[hinklewj]

Thank you, but trying to stay away from vlookup as each month we add a new column, thus pushing the total column over by one. I know there's a formula outside of vlookup that will do this...

 

[Eric W]

Perhaps:

=INDEX(Sheet1!$B$2:$Z$10,MATCH(A13,Sheet1!$A$2:$A$10,0),MATCH("Grand Total",Sheet1!$B$1:$Z$1,0))

Where the customer number is in A13 on the current sheet. Set the ranges so that they far exceed where your data is.