Index + Match - Together they can find any thing..

Index + Match 

 

Thanks you all for your response and again coming here... 

I really got a good response on offset so I thought to explore Index + Match. If you want to follow me, click on Follow me.

Index Match for Images-

Lets talk about one by one so you understand there is no practical use, if you use any of them alone.

Index

There are 2 versions of Index and we will talk about the first one only.

1. Index(array,row_num,[column_num]) 
2. Index(reference,row_num,[column_num],[area_num])

Syntax

• Array - This is a range, it can have both rows or columns. It depends on your data set, see example below-
• A1:A10 - only rows
• A1:J1 - only columns
• A1:J10 - rows + columns  

•  row_num - If you have selected 4 rows for example and you want to take out the row number 2 data then this argument will be 2.
• column_num - If you understand the above one then it is the same logic. If you have selected 3 column and you want to take out column 2 data then this argument will be 2.

Note: In this formula column_num is optional but this is not the case, row_num can also be optional if you put the column_num value and leave the row field blank then system will always show you 1st row data and same applies with column.

Basic understanding - Quite clear, how Index work and you can get the data from array by passing arguments row number and column number.
Ok lets think again, does it make any sense when you are entering numbers manually..Noooooo.
If you already know what data you want to extract you can do it manually. Now this function
has no worth, absolutely right in this way but wait just have some info about Match.

Match

• Match(lookup_value,lookup_array,[match_type])

Syntax

• lookup_value -As simple as that, what you want to search (any string, any number).
• lookup_array - It is same as Index array but there is difference either you can select rows or the columns. You can not combine them.
• match_type -You can put 3 inputs type -1,0,1. For exact match you should enter 0 only, most of the times exact match is used. To know more about this click on Excel Lookup Functions.

Basic understanding - Again no use of this formula, it only gives you the sequence number not the lookup value or in other words it tells you exact position of the lookup value. What will you do with the sequence number. Here comes the Index and Match relationship.

We just talk about Index row_num and column_num, so these are the 2 arguments that can be passed by entire match function (which give you the sequence number). Now you can really explore the power of Index + Match.

Index + Match Example

Thanks for reading all this, I will soon upload the video on you tube.

Please get in touch for any query help or suggestion..
Youtube
Facebook Page
Twitter
Email: lkthecool@gmail.com