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

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
578
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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:

Code:
=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))
 
Last edited:
Upvote 0
Try...

=LOOKUP(9.99999999999999E+307,OFFSET(INDEX('18scores'!B:B,MATCH($A2,'18scores'!A:A,0)),0,0,1,COLUMNS('18scores'!B:CN)))
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
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))
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top