Excel Magic Trick 1502: Lookup Based On Row Number with Data Validation For Row Number

In this video learn how to Lookup Based On Row Number with Data Validation For Row Number. Here are the topics:
1. (00:06) Introduction
2. (01:01) INDEX Function too pull data based on row number
3. (02:16) ROW Function to get first row in data set
4. (02:48) MATCH & REPT Functions to get last row number.
5. (05:23) Data Validation to limit on the row numbers in the correct range
6. (06:41) Text Formula for informative label
7. (07:48) Summary

[DougHExcel]

what a neat way to use the REPT function! Thanks Mike.

[felipe]

you are teaching me so much and Iam really grateful for that! Thanks a lot!

[ismailismaili]

I can't say anything anymore all I have to say thr king of excel formulas is U

[MalinaC]

Nice data validation with a lot of interesting "features", including REPT with 255 characters :). Thank you, Teammate!

[johnborg]

Great trick!!! I use something similar to validate the list of all employees. :) :)

[qasimawan]

Hi Mike, thank you for uploading! Sorry, I still don't understand why you used the 'REPT' function? I used 'Evaluate formula' to try and understand what the calculation engine was doing but no luck!

[rrrprogram]

Fantastic mike... What about using offset function

[athanasiosbarmpas]

Hi sir, excellent work i want to organize a board game tournament and I want to have a league table, but I can not find how I rank the entire row in the first, second, third, place according to criteria most wins for example.Thanks

[LectroDev]

Do you have a tutorial that shows how to generate unique distinct values based on a cell that uses a validation list? Example Store A in validation list returns 15 unique sellers in a column?

[nadirali]

How about a session on maps and 3D maps sometime

[johnborg]

Thanks Mike. Instead of the Match(Rept("Z", 255).... Please correct me if you could also use Match("ZZZZ", B:B) or Match("Omega", B:B). Should that do it also?? OR will it be slower??

[Learn_More_Pro]

Hello sir, I would like to take your interview on my channel so that excel lovers will come to know there are 3000 + magic tricks are available on your channel and then also it will help to lots of job seekers, it will be great inspiration for them if possible let me ....its so simple i will send some questions you just record the video of that answers. and then i will record my video also and edit properly and will upload on my channel.

[sjn]

Couldn't you use counta + start row to get the last row?

[drsteele]

This cookoo-lookin formula will report the row of the last populated cell in ColB. =AGGREGATE(14, 6, (NOT(ISBLANK(B:B))*ROW(B:B)), 1). And this even cooookier lookin one will report the first populated cell in ColB after the header. =1/AGGREGATE(14, 6, 1/(NOT(ISBLANK(B:B))*ROW(B:B)), 1)+1