• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index match question

R

rahulsharma012

New Member
I am attaching an excel file as my query.


In the file in cell b28 i am applying index match formula but its not working showing ref# error.Some minor mistake is their but i am not able to figure it out.


Kindly fix the error with the same index match formula.Plz reply and send the file on hotfile only.


https://hotfile.com/dl/182127952/c943de5/rahuls_query.xlsx.html


Regards,

Rahul
 
It's showing #REF! as you have deleted a cell that it was referring to


What are you trying to show there?
 
Good morning rahulsharma012


Just where is the formula? I have show all formulas and nothing shows!
 
Morning Hui


You must have posted as I was trying to find the formula, your eyes work at a greater speed than mine :)
 
You may want to try in B28:

Code: 
=OFFSET(B$1,MATCH($A28,$A$2:$A$12,0)+($A$27="south")*13,0)

Copy across
 
Hi,


Can you paste the below formula ("B28") and check.


=INDEX($B$15:$G$25,MATCH($A28,$A$15:$A$25,0),MATCH(B$27,$B$14:$G$14,0),MATCH($A$27,$A$14,0))


Thanks,

Suresh Kumar S
 
sorry here is the link i am sending you as attachment in which the formula is in cell B28


https://hotfile.com/dl/182130348/0f135ce/rahulsss.xlsx.html
 
https://hotfile.com/dl/182132207/3d018c3/rahulsss_(1).xlsx.html


this excel file has formula in cell b28 and and data validation is their in cell a27 and a28,so whenever user changes a27 and a28 the corresponding values should change in cells b28:g28.


so formula is already their in b28 but its showing some minor error.


regards,

Rahul
 
Hi rahulsharma012,


In case you want to use OFFSET()


Code: 
=OFFSET(IF($A$27="North",$A$1,$A$14),MATCH($A$28,$A$15:$A$25,0),MATCH(G$27,$B$14:$G$14,0))


The reference error was because the INDEX() as a reference form takes a range as first argument, when you write INDEX(A27,9,1) it implies that index need to look 9 rows down and 01 column to the left of it, since you have selected only single cell, it will give you a reference error, try replacing it with INDEX(A27:C35,9,1), it will not give you this error (bcz now it can look 9 rows down and 01 column to left)


Regards,
 
then how is this file function index and match working with indirect function


i want the same of it.


https://hotfile.com/dl/182138390/43f7dfd/1234.xlsx.html
 
@rahul


Hi


if you would like to use the same formula INDEX and MATCH then you need to create a Name Range which is indicate for the INDEX function


in your file there is two heads one is north and another one is south using with names you have to create the Name Range


please download the below file and check what i did


https://dl.dropbox.com/u/75654703/rahulsss%20%2811%29.xlsx


after download open the file and press the shortcut key: = Ctr+F3 there you find the name Range Box check the name range boxes


hope you can understand other wise please refer us again


Thanks


SP
 
You must log in or register to reply here.
Back
Top