Vlookup column reference is last filled column in a row?

[IndependentHope]

Good evening and thanks for looking at this and possibly helping me out.

Currently, I have a standard vlookup forumla that looks like =Vlookup(B2,Sheet2!A:G,5,false)

However, the data on sheet two is ever expanding--each month, specifically. And I need to return the most recent (so, rightmost filled) column. Right now, the column is 5, but next month it will be 6 and so on and so forth.

I'm only a moderate Excel user...nowhere near wizard status. I have exactly no experience with VBA and basically learn formulas via Google when I need to figure something out. But Google doesn't seem to follow what I'm asking. But this forum has helped me with about a gazillion other Excel problems, so I figured I'd give posting this issue specifically.

Thanks for all your help!

 

[Aladin Akyurek]

What is your VLOOKUP formula supposed to return - a text or numeric value? And in which row starts the table data (including the headers) in Sheet2?

 

[IndependentHope]

What is your VLOOKUP formula supposed to return - a text or numeric value? And in which row starts the table data (including the headers) in Sheet2?

It is supposed to return a number--the total count of monthly "tallies" for that client. Including headers, the data starts in row 1.

 

[Aladin Akyurek]

It is supposed to return a number--the total count of monthly "tallies" for that client. Including headers, the data starts in row 1.

Does the following return the last column (number) with data underneath in your lay-out?

=LOOKUP(REPT("z",255),1:1)

 

[IndependentHope]

Does the following return the last column (number) with data underneath in your lay-out?

=LOOKUP(REPT("z",255),1:1)

That is a negative. It returns the value of the cell rather than the column number.

 

[Jonmo1]

The 'easy' solution (though definately not the 'best' solution)

=Vlookup(B2,Sheet2!A:G,COLUMNS(Sheet2!A:G),false)

Now if you want to accomodate for adding a new column at say H, and the formula should change to cover A:H
=Vlookup(B2,Sheet2!A:H,COLUMNS(Sheet2!A:H)-1,false)
Note, this is intended to be for the original formula going from A:G

 

[Aladin Akyurek]

That is a negative. It returns the value of the cell rather than the column number.

Sorry. Try...


=MATCH(REPT("z",255),1:1)

 

[IndependentHope]

Sorry. Try...


=MATCH(REPT("z",255),1:1)

Aha! Success! This works beautifully! Thank you sooo much!