Assignment 5 Conditional Formatting Conditional Formatting (Student Detail)

 

Conditional Formatting (Student Detail)

Formula uses in Lookup table cells Name, Age, Contact, Course, Batch.

Index Function :- The Excel index function return the value at a given position in a range or array you can use index to retrieve individual values or entire rows and columns. Index is often used with the Match function, where Match locates and feeds a position to index.

Match Function :- The Excel Match Function is use to locate the position of a lookup value in a row, column or table.

Function use for lookup Name, Age Course, Contact, Batch

=INDEX($B$5:$H$14,MATCH($E$17,$E$5:$E$14,0),MATCH(C19,$B$4:$H$4,0))

INDEX:- Index only does one thing it is to index an array.

MATCH:- Match is used to fr lookup the match value from the index array.

$B$5:$H$14 (Cell range)

$E$17 (Cell Address)

0 (Exact match)

Note:-

If we select ID number in ID cell from drop-down button then lookup all details in function cells like Name, Age, Contact, Course, Batch.

And highlight row in that table as according to ID number which will be selected from drpdown list.