• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

To find the name

V

VARGEESH

Member
I have a student list. the format is given below.


COLUMN A COLUMN B

Stud_Name Stud_No


Ragul 101

Abishek 102

Guna 105

Jenifer 108

Lenin 104


While entering the student no in C1 the student name should display in D1.


For that i am using =IF(AND(C1=B1,A1,IF(AND(C2=B2,A2,IF(AND(....


The formula is enough when the data is low.


But, i have upto 50000 values.


Kindly advice me to use the correct formula.
 
Try using this ?


Paste this in column D and if you input any code in Col C the name should appear.


=INDEX(A1:A50000,MATCH(C1,B1:B5000,0))


HTH


Regards.
 
I don't have an idea how use it. Because all time we enter in a same cell.


can you explain me how to use vlookup for this scenario.
 
Disregard... written while Vargeesh was entering his previous response.


Vargeesh,


MrZ's formula works EXACTLY like a VLOOKUP where you don't lookup the value from the first column, but from any column. In this case the lookup value is found in column 2 (B), and the value you're looking for is put into the C1 cell.


MrZ's formula says:


Look in cell C1. If you can find the C1 value between B1 and B50000, give me the A-row value that corresponds to the row where you found the data in the B column.


With your column headings, MrZ's formula could be re-worded to say: If C1 is in the Student Number column, tell me Student name. His formula goes into D1. The Student number goes into C1.


Note that MrZ's formula left out one zero. It should be

=INDEX(A1:50000,MATCH(C1,B1:B50000,0))


Have you tried Mr Z's formula?


Don
 
I am using this formula =INDEX(A1:A50000,MATCH(C1,B1:B50000,0))


And if the entered number is not in the student list it should display a message like "Does not Exist".


How can i insert the message in the same code.
 
Try this please


=IF(ISNA(INDEX(A1:A50000,MATCH(C1,B1:B50000,0))),"Does not exist",INDEX(A1:A50000,MATCH(C1,B1:B50000,0)))


Cheers,


Z.
 
You must log in or register to reply here.
Back
Top