Trying to do an Index with small if for the first time

[Cobbhill]

Hi I am trying to retrieve the quantity for a part number from all locations in another workbook. The following is close but it brings back the quantity from 2 part numbers below the ones I want. Any help would be appreciated, I am very new to Excel.

=INDEX([FSL_Inventory.xls]DATA!$D$3:$J$34159,SMALL(IF([FSL_Inventory.xls]DATA!$D$3:$J$34159=$A$5,ROW([FSL_Inventory.xls]DATA!$D$3:$J$34159)),ROWS(1:1)),3)

[Ace_XL]

perhaps change to

=INDEX([FSL_Inventory.xls]DATA!$D$1:$J$34159,SMALL(IF([FSL_Inventory.xls]DATA!$D$3:$J$34159=$A$5,ROW([FSL_Inventory.xls]DATA!$D$3:$J$34159)),ROWS(1:1)),3)

[Cobbhill]

Ace_XL, The have headers in D1 and D2

[Ace_XL]

Please Login or Register  to view this content.

This portion of the formula will not return a value less than 3, hence you will always INDEX 3 rows from DATA!$D$3:$J$34159 i.e row 5 even if the D3 = A5, hence start 2 rows higher

[Cobbhill]

I do not understand. Are you saying because I do not use d1, that I will always be off by 2? the 1st occurance of the part number is d1561, I want to return e1561..I sort of thought the problem might be with the end of the formula Rows(1:1)),3) but I have tried to change several times

[Ace_XL]

the problem lies here...

ROW([FSL_Inventory.xls]DATA!$D$3:$J$34159) starts with Row 3!

You could simply change to
=INDEX([FSL_Inventory.xls]DATA!$D$3:$J$34159,SMALL(IF([FSL_Inventory.xls]DATA!$D$3:$J$34159=$A$5,ROW([FSL_Inventory.xls]DATA!$D$3:$J$34159)),ROWS(1:1))-2,3)

[Cobbhill]

I tried changing d3 to d1 and when I got the same result tried -2,3 after rows(1,1)...received the same

[Cobbhill]

t values should be used for "ROWS(1:1))-2,3" part of the statement...???

[JBeaucaire]

There are two solutions. The simplest in my mind is change all the D3 references to D1. Ace showed in post #2 that same idea, but only changes one, you have to change them all. This will work. Rows 1 and 2 are headers so they will never match your criteria and thus will always be skipped, but the result is the rows will now always match the INDEX range.

=INDEX([FSL_Inventory.xls]DATA!$D$1:$J$34159,SMALL(IF([FSL_Inventory.xls]DATA!$D$1:$J$34159=$A$5,ROW([FSL_Inventory.xls]DATA!$D$1:$J$34159)),ROWS(1:1)),3)

The less attractive solution in my mind is to "adjust" the row() references by -2.

=INDEX([FSL_Inventory.xls]DATA!$D$3:$J$34159,SMALL(IF([FSL_Inventory.xls]DATA!$D$3:$J$34159=$A$5,ROW([FSL_Inventory.xls]DATA!$D$3:$J$34159)-2),ROWS(1:1)),3)

[daddylonglegs]

The standard method I use is to subtract the ROW of the first cell and add 1, i.e.

=INDEX([FSL_Inventory.xls]DATA!$D$3:$J$34159,SMALL(IF([FSL_Inventory.xls]DATA!$D$3:$J$34159=$A$5,ROW([FSL_Inventory.xls]DATA!$D$3:$J$34159)-ROW([FSL_Inventory.xls]DATA!$D$3)+1),ROWS(1:1)),3)

Of course it's longer but more robust. If you add or delete rows at the top of the sheet the formula results won't change. For the same reason I prefer to base ROWS function on the start cell, e.g. if the first formula is in D3 change ROWS(1:1) to match - i.e. ROWS(D$3:D3)