find field value for specific value in Excel
Q24. Given a raw data Table, how can I find the value of a field for a specific value of another field?
For example, from a class marks table, given a Student First Name, how do I get his / her marks?
To do it in Excel, here is the answer:
a) Enter the formula =INDEX(V3:V12,MATCH(Y3,T3:T12)) where T3:T12 corresponds to all First names in Table, V3:V12 has marks data and Y3 corresponds to the first name of student for whom the marks detail is required.
MATCH returns the relative position of the cell in the "First Name" range that matches the user specified "First Name". INDEX function uses this value as Index to get the marks from range with "Marks".