Index match on multiple criteria with multiple sheets

[amjadas1987]

i have a workbook with 4 sheets 1st three are Jan, Feb, Mar and 4th one is home. Columns are style no., qty and unit price. i want to check index match the unit price of mentioned style number from all three sheets. Like i want a formula in HOME sheet in cell C2 which see style no. in cell A2 and then check it's unit price in all three sheets and put unit price of matched style no.

Three sheets Jan, Feb, Mar are like this.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Style No.[/TD]
[TD]Qty[/TD]
[TD]Unit Price[/TD]
[/TR]
[TR]
[TD]WTC123456[/TD]
[TD]456[/TD]
[TD]45,000[/TD]
[/TR]
[TR]
[TD]WTB281654[/TD]
[TD]1000[/TD]
[TD]65,000[/TD]
[/TR]
</tbody>[/TABLE]

HOME Sheet.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Style No.[/TD]
[TD]Qty[/TD]
[TD]Unit Price[/TD]
[/TR]
[TR]
[TD]WTC123456[/TD]
[TD]456[/TD]
[TD]here i want formula[/TD]
[/TR]
[TR]
[TD]WTB281654[/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

because some styles are put in Jan and some are in Feb so on. but home sheet has all the styles no.

Thanks

 

[gruntingmonkey]

Hello,

Formula as below:
=IFERROR(VLOOKUP(A2,Jan!$A$2:$C$5,3,FALSE),IFERROR(VLOOKUP(A2,Feb!$A$2:$C$5,3,FALSE),VLOOKUP(A2,Mar!$A$2:$C$5,3,FALSE)))

Explanation
=IFERROR(VLOOKUP([Style Number],[TabName]![Range in tab names],[Unit Price],FALSE),IFERROR(VLOOKUP(A2,Feb!$A$2:$C$5,3,FALSE),VLOOKUP(A2,Mar!$A$2:$C$5,3,FALSE)))

This formula checks Jan tab, then Feb Tab then March tab.

 

[amjadas1987]

Thanks for your great reply but with apologize i already doing it with vlookup but vlookup is slow and can see only right side. i would you like to ask why we cannot do it with the use of Index and Match only like if i use it for only one tab it works great but i am not understanding how to make it for all 12 months (12 sheets).

Formula:
=INDEX(Jan!D2:D31,MATCH(A2,Jan!B2:B31,0))
Explaination:
=INDEX(Jan![UnitPrice],MATCH([Style No.],Jan![Style No.],0))

12 months 12 sheets pattern

PO No. Style No. Qty Unit Price
4600000348 WTB181693 470 51.00
4600000310 WTB371652 347 59.10
4600000298 WTB371652 350 59.10
4600000358 WTC181281 688 213.00
4600000392 WTC181275 300 41.75
4600000415 WTC181304 200 64.60



Home Sheets Pattern

Style No. Qty. Unit Price
WTB181693 670 51.00 [Here my above formula is working perfect but only for one sheet]
WTB371652 697 [Here need same formula but search in all 12 sheets] [i also created 12 months list with "months"]
WTC181281 888
WTC181275 794
WTC181304 786

hope now you can understand me properly.

Thanks

 

[gruntingmonkey]

This can be done in the same way.

I have done this up to May, but you should be able to work out how to add the rest of the year

=IFERROR(INDEX(Jan!$C$2:$C$31,MATCH(Home!A2,Jan!$A$2:$A$31,0)),IFERROR(INDEX(Feb!$C$2:$C$31,MATCH(Home!A2,Feb!$A$2:$A$31,0)),IFERROR(INDEX(Mar!$C$2:$C$31,MATCH(Home!A2,Mar!$A$2:$A$31,0)),IFERROR(INDEX(Apr!$C$2:$C$31,MATCH(Home!A2,Apr!$A$2:$A$31,0)),INDEX(May!$C$2:$C$31,MATCH(Home!A2,May!$A$2:$A$31,0))))))

 

[amjadas1987]

ok thanx for reply but fomula is too long

i am using this formula to summing Qty of all 12 sheets

=SUMPRODUCT(SUMIF(INDIRECT("'"&months&"'!$C$6:$C$31"),B18,INDIRECT("'"&months&"'!$D$6:$D$31")))

i created a list of all tabs name and gave it a name "months" and formula is working good

why we cannot use this "months" a list name of 12 months sheet for index match. hope you will do it.