Lookup Function
MATCH
|
Name |
Date of Reg |
Marks |
|
AARTI |
01-04-2023 |
25 |
|
Ritu |
02-04-2023 |
35 |
|
CHAMA |
03-04-2023 |
45 |
|
Gita |
04-04-2023 |
60 |
|
Ritu |
05-04-2023 |
62 |
|
James |
06-04-2023 |
80 |
|
Match |
2 |
=MATCH(A7,A6:A11,0)
INDEX
|
Index |
01-04-2023 |
Index show second row of second column like matrix
=INDEX(A5:C11,2,2)
|
Enrollment No |
Candidate Name |
Birth Date |
Father Name |
Paper-1 |
|
21115073 |
AAFREEN |
26-01-2003 |
JAHANGEER |
V0001001 |
|
21115073 |
AAFREEN |
26-01-2003 |
JAHANGEER |
V0001001 |
|
21115074 |
AAFREEN |
25-02-2003 |
AAFTAB |
V0001001 |
|
21115075 |
AAFREEN JEBA
|
16-05-2004 |
RAHESUDEEN |
V0001027 |
|
21115085 |
AARTI |
16-09-2002 |
KIRAN PAL |
V0001001 |
|
21115086 |
AARTI |
16-04-2001 |
VIJAYPAL |
V0001001 |
|
21115181 |
ANSHU RANI
|
15-12-2002 |
RAM KISHAN |
V0001001 |
|
21115217 |
BHAVNA |
03-08-2001 |
VIJENDRA |
V0001001 |
|
21115223 |
CHAMA |
05-10-2004 |
KRISHANPAL |
V0001027 |
|
21115275 |
GEETIKA |
29-06-2003 |
ARVIND |
V0001001 |
|
21115281 |
GUNJAN RUHELA
|
14-07-2003 |
RAJEEV KUMAR RUHELA |
V0001027 |
|
21115290 |
HIMANI KUSHWAH
|
15-09-2001 |
RAKESH KUSHWAH |
V0001027 |
|
21115312 |
IQRA |
13-07-2007 |
SHAKEEL AHMAD |
V0001001 |
VLOOKUP
|
Enrollment No |
Candidate Name |
Paper-1 |
|
21115073 |
AAFREEN |
V0001001 |
|
21115075 |
AAFREEN JEBA
|
V0001027 |
|
21115290 |
HIMANI KUSHWAH
|
V0001027 |
|
21115074 |
AAFREEN |
V0001001 |
VLOOKUP($A39,$A$22:$E$34,2,FALSE)
|
Vlookup using match function |
|
||
|
Enrollment No |
Candidate Name |
Paper-1 |
Birth Date |
|
21115073 |
AAFREEN |
V0001001 |
26-01-2003 |
|
21115075 |
AAFREEN JEBA
|
V0001027 |
16-05-2004 |
|
21115290 |
HIMANI KUSHWAH
|
V0001027 |
15-09-2001 |
|
21115074 |
AAFREEN |
V0001001 |
25-02-2003 |
VLOOKUP($A46,$A$22:$E$34,MATCH(B$45,$A$21:$E$21,0),FALSE)
|
HLOOKUP |
||||
|
Sr.No |
Enrollment No |
Candidate Name |
Paper-1 |
Father Name |
|
5 |
21115075 |
AAFREEN JEBA
|
V0001027 |
RAHESUDEEN |
|
4 |
21115074 |
AAFREEN |
V0001001 |
AAFTAB |
|
2 |
21115073 |
AAFREEN |
V0001001 |
JAHANGEER |
|
7 |
21115086 |
AARTI |
V0001001 |
VIJAYPAL |
|
8 |
21115181 |
ANSHU RANI
|
V0001001 |
RAM KISHAN |
|
HLOOKUP(B$54,$A$21:$E$34,$A55,FALSE) |
||||
|
Hlookup using match |
|
|||
|
Enrollment No |
Candidate Name |
Birth Date |
Father Name |
Paper-1 |
|
21115075 |
AAFREEN JEBA
|
16-05-2004 |
RAHESUDEEN |
V0001027 |
|
21115074 |
AAFREEN |
25-02-2003 |
AAFTAB |
V0001001 |
|
21115073 |
AAFREEN |
26-01-2003 |
JAHANGEER |
V0001001 |
|
21115086 |
AARTI |
16-04-2001 |
VIJAYPAL |
V0001001 |
|
21115181 |
ANSHU RANI
|
15-12-2002 |
RAM KISHAN |
V0001001 |
HLOOKUP(B$63,$A$21:$E$34,MATCH($A64,$A$21:$A$34,0),FALSE)
|
Unique Table |
CustomerID |
SalesPersonID |
ShipToAddressID |
TaxAmt |
Freight |
TotalDue |
ModifiedDate |
|
2982540701 |
29825 |
279 |
985 |
1971.5149 |
616.0984 |
23153.23 |
07-06-2011 |
|
2982540793 |
29825 |
279 |
985 |
1814.8608 |
567.144 |
21262.66 |
07-09-2011 |
|
2982540885 |
29825 |
279 |
985 |
7032.2178 |
2197.568 |
82490.94 |
08-12-2011 |
|
2982540975 |
29825 |
279 |
985 |
1732.0409 |
541.2628 |
20304.9 |
07-03-2012 |
|
2982541066 |
29825 |
279 |
985 |
3568.5834 |
1115.182 |
41824.9 |
06-06-2012 |
|
2982541158 |
29825 |
279 |
985 |
3174.6895 |
992.0905 |
37295.94 |
06-09-2012 |
|
2982541250 |
29825 |
279 |
985 |
3448.5566 |
1077.674 |
40510.68 |
07-12-2012 |
|
2982541340 |
29825 |
279 |
985 |
3087.5357 |
964.8549 |
36282.66 |
07-03-2013 |
|
2982541431 |
29825 |
279 |
985 |
3525.2833 |
1101.651 |
41328.43 |
06-06-2013 |
|
2982541523 |
29825 |
279 |
985 |
4613.1486 |
1441.609 |
54244.24 |
06-09-2013 |
|
2982541615 |
29825 |
279 |
985 |
2788.1476 |
871.2961 |
32800.24 |
07-12-2013 |
|
2982541706 |
29825 |
279 |
985 |
3765.6618 |
1176.769 |
44393.22 |
08-03-2014 |
|
2999440701 |
29994 |
282 |
482 |
2775.1646 |
867.2389 |
32474.93 |
07-06-2011 |
|
2956540701 |
29565 |
276 |
1073 |
40.2681 |
12.5838 |
472.3108 |
07-06-2011 |
|
2989840701 |
29898 |
280 |
876 |
2344.9921 |
732.81 |
27510.41 |
07-06-2011 |
|
2989840703 |
29898 |
283 |
849 |
1375.9427 |
429.9821 |
16158.7 |
09-06-2011 |
Vlookup with Match and Single Criteria
|
CustomerID |
TaxAmt |
Freight |
TotalDue |
|
29825 |
1971.5149 |
616.0984 |
23153.2339 |
|
29898 |
2344.9921 |
732.81 |
27510.4109 |
=VLOOKUP($A94,$B$73:$H$88,MATCH(C$93,$B$72:$H$72,0),FALSE)
Vlookup with Match and Multiple Criteria
|
CustomerID |
ModifiedDate |
TaxAmt |
Freight |
TotalDue |
|
29825 |
08-12-2011 |
7032.2178 |
2197.568 |
82490.9371 |
|
29898 |
07-06-2011 |
2344.9921 |
732.81 |
27510.4109 |
=VLOOKUP($A101&$B101,$A$73:$H$88,MATCH(C$100,$A$72:$H$72,0),FALSE)
Vlookup with single Criteria using choose function
|
CustomerID |
TaxAmt |
|
29825 |
1971.5149 |
|
29898 |
2344.9921 |
=VLOOKUP($A106,CHOOSE({1,2},$B$73:$B$88,$E$73:$E$88),2,FALSE)
|
Choose |
||
|
Name |
Grade |
Sales |
|
Ram |
A |
Bed Sheets |
|
Ram |
B |
Towels |
|
Sita |
C |
Vechicle |
|
Arun |
A |
Grocery |
|
Arun |
B |
Wheels |
|
Sales |
Bed Sheets |
|
|
Name |
Ram |
=VLOOKUP(B117,CHOOSE({1,2},C111:C115,A111:A115),2,TRUE)
Vlookup with Multiple Criteria using choose function
|
CustomerID |
ModifiedDate |
TaxAmt |
|
29898 |
09-06-2011 |
1375.9427 |
|
29825 |
06-06-2013 |
3525.2833 |
VLOOKUP($A123&$B123,CHOOSE({1,2},$B$73:$B$88&$H$73:$H$88,$E$73:$E$88),2,FALSE)
Ctrl+Shift+Enter
Post a Comment