Help with an Index match using conditions [Solved]

[lwilt]

Hi all,

I'm trying to use an index matching to move over some pricing data. This formula worked last year but won't work this year for some reason.

the formula I'm using is:
=IF(INDEX(data1!$A$2:$K$10,MATCH(Sheet1!A2,Sheet2!$A$2:$A$10,0),SMALL(IFERROR(COLUMN($A$1:$I$1)/(LEFT(data1!$A$1:$K$1,3)="Qty"),100000),COUNTIFS(A$2:A2,A2)))=0,"",INDEX(data1!$A$2:$K$10,MATCH(Sheet1!A2,data1!$A$2:$A$10,0),SMALL(IFERROR(COLUMN($A$1:$K$1)/(LEFT(data1!$A$1:$K$1,3)="Qty"),100000),COUNTIFS(A$2:A2,A2))))=IF(INDEX(data1!$A$2:$K$10,MATCH(Sheet1!A2,Sheet2!$A$2:$A$10,0),SMALL(IFERROR(COLUMN($A$ 1:$I$1)/(LEFT(data1!$A$1:$K$1,3)="Qty"),100000),COUNTIFS(A$2:A2,A2)))=0,"",INDEX(data1!$A$2:$K$10,MATCH(Sheet1!A2,data1!$A$2:$A$10,0),SMALL(IFERROR(COLUMN($A$1:$K$1)/(LEFT(data1!$A$1:$K$1,3)="Qty"),100000),COUNTIFS(A$2:A2,A2))))

There is also a 2nd formula to do the pricing...the first is to move the qty over that are tied to the pricing. I attached an example file to try and show how it currently looks and what I am trying to get the final product to look like.

If possible could you also help me on the 2nd formula to move pricing over as well? I can post the formula I'm using for that as well if needed but it currently is also not working even though it worked fine for me last year. Don't think anything has changed so I'm not sure what's going on.

thank you.

 

[lwilt]

this is also posted at:

https://www.mrexcel.com/forum/excel...formatting-index-match-count.html#post4736895

http://www.excelforum.com/showthread.php?t=1170978&p=4566572#post4566572

 

[Awesome Wally]

Your attached sheet does not contain a Sheet 2 but your formula references it.

 

[bosco_yip]

Try,

1] In "Sheet1" AJ2, formula copy down :

=IFERROR(1/(1/INDEX(data1!$B$2:$K$10,MATCH($C2,data1!$A$2:$A$10,0),COUNTIF($C$2:$C2,$C2)*2-1)),"")

2] In "Sheet1" AK2, formula copy down :

=IFERROR(1/(1/INDEX(data1!$B$2:$K$10,MATCH($C2,data1!$A$2:$A$10,0),COUNTIF($C$2:$C2,$C2)*2)),"")

Regards
Bosco

 

[lwilt]

Sorry about that Wally...I was trying to rename everything in the orig formula to match the ex file I made.

Bosco...that was perfect. really appreciate it.

Is there a way to make this thread as closed?

 

[Khalid NGO]

Is there a way to make this thread as closed?

Hi @lwilt
Not necessary to close, but your thread is marked as "SOLVED".

Take Care