# Index match help needed

1. ## Index match help needed

Hello all

Looking for some help on a formula

Using the following table below i would like to find the corresponding color that is associated with a certain set of criterias.

For example if the question was for WK3 which color is the number 1 color.

I was using index/match however i could find a way for the match function to find the corresponding column based on "wk3" as the criteria

Wk1 Wk2 Wk3
4 1 3 Red
3 2 6 Blue
1 3 2 Green
5 4 1 Yellow
6 5 4 Black
2 6 5 Brown

Any help would be appreciated
Thanks
James

2. ## Re: Index match help needed

If you are using index and match for this data. Your data is not suitable.

But if this is your data and your request is to find which color is rank first in week 3, you will require some other formula.

To arrange the data to be suitable for index and match. It look like this

1 Wk1 Wk2 Wk3
1 Red Blue Yellow
2 Blue Yellow Green
3 Yellow Green Black
4 Green Black White
5 Black White Red
6 White Red Blue

If you are unable to rearrange your data, the formula that I could provide would be slightly complicated than index and match.

3. ## Re: Index match help needed

thanks for the reply. unfortunately the data is organized this way and it is a part of a large set of data which would be cumbersome to rearrange as you suggested. Could you point me in the direction of this complicated formula you mention?

4. ## Re: Index match help needed

hi James, try:
=INDEX(D2:D7,MATCH(1,INDEX((A2:A7,B2:B7,C2:C7),,,MATCH("Wk3",A1:C1,0)),0))

the one in red font can be referred to a cell reference too

Edit: or another like
=INDEX(D2:D7,MATCH(1,INDEX(A2:C7,,MATCH("wk1",A1:C1,0)),0))

5. ## Re: Index match help needed

try :
=Offset(a1,match("1",c2:c7,0),match("Wk3",a1:c1,0)-1))

change ranges and references to actual

Hope this helps

Edit-
or
=Index(A1:C7,match("1",c1:c7,0),match("Wk3",a1:c1,0))

6. ## Re: Index match help needed

Benishiryo - Thanks for the help. I am all set now. Very much appreciated.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1