Vlookup to last cell

[mtharnden]

hello, i have done some searching and not found an answer yet

i am working on something that requires referencing to a different workbook for each day for 3 months (so 90 different workbooks).

now column A on every workbook will be my lookup value
each workbook has a different number of columns but the last column will always be a "total" coumn.

i want to pull the Total Coumn from each workbook refrencing to my lookup value to column A (i do not want all the rows in these workbooks, just the ones i want to refrence to)

i already know how to make the lookups to reach the needed workbooks, i just need to know how to get it to be the last cell with data in it, i am guessing it needs to be something that will count columns to use for the index

example:


reference workbooks:
Lookup match A B C Total
DG 0 1 1 2
D8 2 4 1 7
1B 0 2 0 2
C2 2 1 3 6
4T 4 0 2 6


results i need:
Lookup Value Day1 Day2
DG 2 4
1B 2 5
C2 6 6

 

[mtharnden]

sory about the spacing on the examples, the data doesnt match up to the heading, but you can still see what i am looking for

 

[Jonmo1]

Try using a MATCH formula in the 3rd argument (ColIndex) in the vlookup...

Something like..

VLOOKUP(A1,Othersheet!A1:Z1000,MATCH("Total",Othersheet!A1:Z1,0),FALSE)

 

[T. Valko]

hello, i have done some searching and not found an answer yet

i am working on something that requires referencing to a different workbook for each day for 3 months (so 90 different workbooks).

now column A on every workbook will be my lookup value
each workbook has a different number of columns but the last column will always be a "total" coumn.

i want to pull the Total Coumn from each workbook refrencing to my lookup value to column A (i do not want all the rows in these workbooks, just the ones i want to refrence to)

i already know how to make the lookups to reach the needed workbooks, i just need to know how to get it to be the last cell with data in it, i am guessing it needs to be something that will count columns to use for the index

example:


reference workbooks:
Lookup match A B C Total
DG 0 1 1 2
D8 2 4 1 7
1B 0 2 0 2
C2 2 1 3 6
4T 4 0 2 6


results i need:
Lookup Value Day1 Day2
DG 2 4
1B 2 5
C2 6 6

It's not real clear where the data is you want to find. It sounds like you want the LAST numeric value from some range.

To return the last (right-most) NUMERIC value from row 1:

=LOOKUP(1E100,1:1)

To return the last (bottom-most) NUMERIC value from column A:

=LOOKUP(1E100,A:A)

 

[mtharnden]

That doesn't help with looking up the lookup value..,

All the files I am referencing to are on a network drive
Here's the formula I am looking for:

=vlookup(A3,'[file1.xls]Pivot'!$1:$65536,XXX,0)

File1 would be the workbook
Pivot the sheet - there's alWays different #of rows and columns

XXX is my missing part, the col_index_num
Instead of a set # I want it to just find the last cell with data

 

[mtharnden]

Also note that the row# always changes for the data, being the reason for the lookup

 

[Jonmo1]

You said...

each workbook has a different number of columns but the last column will always be a "total" coumn.

does that mean the word Total will appear in row 1 ?
Then

Replace XXX (in the formula you just posted)
With
MATCH("Total",'[file1.xls]Pivot'!$1:$1,0)

 

[mtharnden]

You said...



does that mean the word Total will appear in row 1 ?
Then

Replace XXX (in the formula you just posted)
With
MATCH("Total",'[file1.xls]Pivot'!$1:$1,0)

PERFECT!
that's exactly what I needed, thanks