SOLVED

Contributor

# Index Match From another Sheet

Hello,

I have attached a file where I have to bring some cell value from another sheet using Index/Match Function.

Can anyone help me regarding this!

7 Replies

# Re: Index Match From another Sheet

@najim_uddin Clearly, you are working on an assignment, an Excel proficiency test or some kind of exam that is giving you points per task. Yesterday, 6 points for COUNTIF and SUMIF and one of the members here did the work for you. Today, 6 points for INDEX and MATCH. May I suggest  that you work this out on your own by looking in the MS support documentation and/or other on-line resources.

# Re: Index Match From another Sheet

@Riny_van_Eekelen, actually, i am doing a job. its not assignment purpose. I am learning from YouTube, where i found a link for practice. Yesterday, one of the members suggested, COUNTIF but that doesn't work. As i am using MS-EXCEL 13 version, cant using the FILTER and SORT options too. You can check my previous posts also.
best response confirmed by najim_uddin (Contributor)
Solution

# Re: Index Match From another Sheet

@najim_uddin Try following formula and drag down and across as needed.

``=INDEX(Data!\$B:\$D,MATCH(\$B9,Data!\$A:\$A,0),COLUMN(A\$1))``

# Re: Index Match From another Sheet

Thanks @Harun24HR, it works but can you please explain why you added COLUMN(A\$1), in the last? I tried several times without COLUMN(A\$1).

# Re: Index Match From another Sheet

I have added COLUMN(A\$1) to make column reference dynamic for INDEX() function so that you can just drag the formula for right columns. You can use =INDEX(Data!\$B:\$B,MATCH(\$B9,Data!\$A:\$A,0)) without COLUMN(A\$1) but in this case you have to use 3 formula for 3 different column like, =INDEX(Data!\$B:\$B,MATCH(\$B9,Data!\$A:\$A,0)), =INDEX(Data!\$C:\$C,MATCH(\$B9,Data!\$A:\$A,0)), =INDEX(Data!\$D:\$D,MATCH(\$B9,Data!\$A:\$A,0))

# Re: Index Match From another Sheet

Thanks, for enlightening me!!

# Re: Index Match From another Sheet

Many thx for posting that. It was a big help!