By using index & match functions how can I apply them in my workbook and retrieve the data from the table array inside another different workbook?

for example in workbook1 you can type that equation:

=index(the list array from [workbook2]sheet1!B:B;match(a value from [workbook1]shee1!a1; array from [workbook2]sheet1!A:A;0))

we can use index and match formulas together to get a value from any location in my workbook or another workbook located on my pc or on network. index formula will retrieve the values that match formula will match it exactly with the desired and correct value for example: 

 

=index(array;match(value;array;0 for exact match))

 

i have two columns a and b, and i have value entered in another column that may be equal to a value in column a. now i want to first match the value i have with the value in column a to match it and match will return back the row number for that value which index will take that value and apply it on next column to retrieve the right and correct value.

 

in our example we need to get the values from different workbook so i will apply the upper index and match formulas together and applying the workbook reference to each of them, for example if i have two workbooks X and Y, so if we want to get a value from Y to X we need to type the name of the workbook first like this [Y] followed by the worksheet name and then the range desired:

[Y]sheet1!a1:a10

 

now the final function will be:

 

 =index([Y]sheet1!B:B;match([X]sheet1!a2;[Y]sheet1!A:A;0))

After checking if a cell value exists in a column, I need to get the value of the cell next to the matching cell. For instance, I check if the value in cell A1 exists in column B, and assuming it matches B5, then I want the value in cell C5.

To solve the first half of the problem, I did this...

=IF(ISERROR(MATCH(A1,B:B,0)), "No Match", "Match")

...and it worked. Then, thanks to an earlier answer on SO, I was also able to obtain the row number of the matching cell:

=IF(ISERROR(MATCH(A1,B:B,0)), "No Match", "Match on Row " & MATCH(A1,B:B,0))

So naturally, to get the value of the next cell, I tried...

=IF(ISERROR(MATCH(A1,B:B,0)), "No Match", C&MATCH(A1,B:B,0))

 

...and it doesn't work.

If you’re using Excel and you’ve already learned how to use INDEX MATCH, you’re well on your way to becoming proficient with Excel lookups.  What INDEX MATCH MATCH offers you is a more powerful version of the formula.  Instead of just a vertical lookup, INDEX MATCH MATCH allows you to perform a matrix lookup, which is also known as a two-way lookup.  This combination formula may initially seem complex because of its three individual formulas, but after you understand each component and how they interact, using this tool will become second nature to you.  INDEX MATCH MATCH is one of several lookup formulas you should learn to become adept in database theory.

Objective and When to Use

There’s really just one key condition that needs to be met before you can use INDEX MATCH MATCH:

A matrix lookup can only work if your data table has lookup values on both the top and left hand side.

Basically, your data needs to be in a matrix format.  People usually create matrixes, with lookup values both vertically and horizontally, to cross reference two different fields.

I AGREE WITH ALL ANSWERS >>>>>>>>