Two way lookup :-
Index function returns a value from within a table or range and Match function finds position for an item.
If you need to find an item value from a table where you have multiple rows and columns, then you need first to find the position of a row and a column for a particular item.
To find the position of row - use match function..
To find the position column - again use match function.
To returns value from relative position of row and column - use index functiin.
As in our example - Finding position of Year 2003. Match function is used. Match 2003 in Range E4:E9 (a single column range) where type is 0 means finding exact match. Result would be 4 which means position of row is 4.
Next, Finding position of Quarter Q2. Match function is used. Match Q2 in Range E4:I4 (a single row range) where type is 0 means finding exact match. Result would be 3 which means position of column is 3.
Next, finding an item value of interestion of row and column . Use Index function where array is E4:I9 which is whole table including headings. Index(table_array, row position, column position). Result will be $350
Thank you!
Index function returns a value from within a table or range and Match function finds position for an item.
If you need to find an item value from a table where you have multiple rows and columns, then you need first to find the position of a row and a column for a particular item.
To find the position of row - use match function..
To find the position column - again use match function.
To returns value from relative position of row and column - use index functiin.
As in our example - Finding position of Year 2003. Match function is used. Match 2003 in Range E4:E9 (a single column range) where type is 0 means finding exact match. Result would be 4 which means position of row is 4.
Next, Finding position of Quarter Q2. Match function is used. Match Q2 in Range E4:I4 (a single row range) where type is 0 means finding exact match. Result would be 3 which means position of column is 3.
Next, finding an item value of interestion of row and column . Use Index function where array is E4:I9 which is whole table including headings. Index(table_array, row position, column position). Result will be $350
Thank you!
No comments:
Post a Comment