How to call the cell in the last available column rather than the specified column in VLOOKUP?

[Rnkhch]

Hello,

In my table of students/scores, I sometimes need to call a score from a certain date (column). For example, I use the following formula in a different (main) sheet:

B2=VLOOKUP(A2,'18scores'!A:CN,50,FALSE)

This works for most students, but some students don't have the entire range of scores, so I get an error, and this error in turn messes up with subsequent formulas.

How can I call the last "available" score (which would be in some column earlier than 50) by using the same formula, i.e. without having to manually change the formula for each such student? (In other words, I would want to get the score in column 50 if it exists, but if it doesn't, then I want the last available score.)

Thanks a lot!


Also thanks for all the help I have received so far in this forum, which has significantly simplified my work and saved me numerous hours! This is the number one forum

 

[inactiveuserps07]

It's always hard to give a definitive solution without seeing the ranges and data. And I'm not sure why your lookup range extends to Column CN (a span of 96 columns) if you only need to search to column 50 (Column AX). But you can see if this works for you:

=LOOKUP(10000,OFFSET(INDIRECT(CELL("address",INDEX([COLOR=#333333]'18scores'!A:A[/COLOR],MATCH(A$2,[COLOR=#333333]'18scores'!A:A[/COLOR],0)))),0,1,1,49))

 

[Aladin Akyurek]

Try...

=LOOKUP(9.99999999999999E+307,OFFSET(INDEX('18scores'!B:B,MATCH($A2,'18scores'!A:A,0)),0,0,1,COLUMNS('18scores'!B:CN)))

 

[Rnkhch]

Thanks a lot! I got Aladin's formula to work. It's amazing

Basically my table consists of 433 rows, each for one student, and column A is the names. Columns B to CN are scores for quizzes/assignments/exams/etc. based on dates. so the header of each column is a date and type of activity that is scored. The reason I needed this formula is to easily pull up how each student performed on any given activity. But since some students miss certain activities, I need to know the last available score on or prior to that date.

Thanks again!

 

[inactiveuserps07]

Yes, it's the same formula. I just chose "10000" since that will be higher than any grade, and I filled in "49" instead of counting columns, because you said you wanted only as far as column 50. It seems to me there is even more flexibility to be built into it by supplying a date and also doing a search "that far"; but if you have what you need, great!

 

[Aladin Akyurek]

It's not the same formula. Besides your personal "10000", your version is too volatile, thus less efficient.

 

[Tetra201]

The formula from Post # 3 is volatile, too (in using OFFSET) and is overcomplicated.

Here is a simpler, non-volatile one:

=LOOKUP(2^1023,INDEX('18scores'!B:CN,MATCH(A2,'18scores'!A:A,0),0))

 

[Aladin Akyurek]

I agree. Even OFFSET should go. It should have been:

=LOOKUP(9.99999999999999E+307,INDEX('18scores'!B:CN,MATCH($A2,'18scores'!A:A,0),0))

 

[Rnkhch]

Thanks a lot everybody!