maching item from main data tab and picking correct $ amount

[Ninocka]

I am creating inventory count sheet
I have all items on one sheet - and multiple tabs for different places where counts are done
so if I go to one of the tabs and enter vendor and item number I can use index and mach 2 criteria array formula to find the item name from the main data sheet
so now each item has two counting units option, (like case and smaller count unit) with associated $ amount
on my counting sheet I want only to pick one counting unit, how can I say - if I find the item - mach counting unit for this item to the price on the main tab

a b c d e f g
vendor item# name case $ for case bottle $ price for bottle

my problem is that I have large range of different counting units in column d as well as for column f
I might put them down to 10 -12 and have list to choose from for counting unit, but how to find the item (index and mach) and than based on counting unit choose colum e or g
and I dont want to have 2 unit count options, since I can see the problem were counts will be entered in the wrong column

thanks

[sourabhg98]

Hey there,
It would be good if you can attach a sample worksheet so that we can have a better look at your data.

You can attach a sample workbook by clicking on "Go Advanced" button at the bottom of the post and then selecting the "Paper Clip" icon from the tools above.

[Ninocka]

Thanks - so I want to pick up in column I counting unit, and that base on that unit to pick up cost from sheet1

[hemesh]

may be column J
=SUMIFS(Sheet1!$K$5:$K$12,Sheet1!$A$5:$A$12,'PM sec2'!A5,Sheet1!$B$5:$B$12,'PM sec2'!B5,Sheet1!$E$5:$E$12,'PM sec2'!E5,Sheet1!$H$5:$H$12,'PM sec2'!H5)
drag down

[Ninocka]

it does pick up but if I change unit counted on PM tab, it does not change $ amount, I need to be updating sheet1 values, and have it updated for value in unit counted on other tabs
I have only one counting unit as example, but I will have other tabs with different items, or just couple of them
also if I change unit counted on pm tab and I don't have that unit for that item on main sheet1 I need it to give me error and recognize it is not available

[hemesh]

Try below in I5 array entered (using control, shift and enter together)

=INDEX(Sheet1!$J$5:$J$12,MATCH(A5&B5&D5&E5&G5&H5,Sheet1!$A$5:$A$100&Sheet1!$B$5:$B$100&Sheet1!$D$5:$D$100&Sheet1!$E$5:$E$100&Sheet1!$G$5:$G$100&Sheet1!$H$5:$H$100,0))

and drag down

in D5 update formula to

=IF(INDEX(Sheet1!$D:$D,MATCH(1,('PM sec2'!$A5=Sheet1!$A:$A)*('PM sec2'!$B5=Sheet1!$B:$B),0))="","",INDEX(Sheet1!$D:$D,MATCH(1,('PM sec2'!$A5=Sheet1!$A:$A)*('PM sec2'!$B5=Sheet1!$B:$B),0)))

drag down

In E5 Update formula to

=IF(INDEX(Sheet1!$E:$E,MATCH(1,('PM sec2'!$A5=Sheet1!$A:$A)*('PM sec2'!$B5=Sheet1!$B:$B),0))="","",INDEX(Sheet1!$E:$E,MATCH(1,('PM sec2'!$A5=Sheet1!$A:$A)*('PM sec2'!$B5=Sheet1!$B:$B),0)))

Drag down


In column J you can then use formula from earlier post or you can use one more column With and & in first formula within index and match

Is this you are looking for?

[Ninocka]

column D and E are perfect, but I is not what I am looking for, In I there should be drop list maybe ( I know how to do this) so if I choose lb or case lets say for lobster I need price in J to change with formula
Sorry for not answering before, I was out with flu

[Ninocka]

Help please

[Ninocka]

This is what I came up with, and it works, up to last part - where if I change unit on pm sheet for item, it does not give me correct price but N/A

=IF(INDEX(Sheet1!$H:$H,MATCH(1,($A8=Sheet1!$A:$A)*($B8=Sheet1!$B:$B),0))="","",IF(INDEX(Sheet1!$J:$J,MATCH(1,($A8=Sheet1!$A:$A)*($B8=Sheet1!$B:$B)*($I8=Sheet1!$J:$J),0))=I8,INDEX(Sheet1!$K:$K,MATCH(1,($A8=Sheet1!$A:$A)*($B8=Sheet1!$B:$B)*($I8=Sheet1!$J:$J),0)),INDEX(Sheet1!$M:$M,MATCH(1,($A8=Sheet1!$A:$A)*($B8=Sheet1!$B:$B),0))))

bit of I only have last part = INDEX(Sheet1!$M:$M,MATCH(1,($A8=Sheet1!$A:$A)*($B8=Sheet1!$B:$B),0) it does give me correct price, so it is somewhere in the formula conflict and I just cannot figure it out

[JohnTopley]

Post an updated file: I assume you have a drop down to change unit?