Looking up values

[gordo]

Someone please help.
I have a workbook which contains the following information:

SKU Loc
191400 A1012
191400 A1015
191400 D6012
191400 E1019
191400 h1004

I have a seperate workbook which displays the sku once. When i use
vlookup it brings back the first loc in this example would be A1012,
however is there a formula where i can bring the second value back
(A1015). Please note that this workbook changes as do the cell values
so using =cell# is not an option.

Please help

 

[Ron Rosenfeld]

Someone please help.
I have a workbook which contains the following information:

SKU Loc
191400 A1012
191400 A1015
191400 D6012
191400 E1019
191400 h1004

I have a seperate workbook which displays the sku once. When i use
vlookup it brings back the first loc in this example would be A1012,
however is there a formula where i can bring the second value back
(A1015). Please note that this workbook changes as do the cell values
so using =cell# is not an option.

Please help

Here's one way:

1. Your list of SKU's is in a named column named "SKU" or a range reference
that does not represent an entire column.
2. Your list of Loc's is in a named column named "Loc" or a range reference
that does not represent an entire column.
3. A1:= the SKU you are searching for
4. A2:= the instance that you are looking for (1,2, etc.)
5. If you type in an instance greater than the maximum, as written the formula
will return an error.

This is an *array* formula, so after you type or paste it in, hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula.

=INDEX(Loc,LARGE((A1=SKU)*ROW(SKU),COUNTIF(SKU,A1)-A2+1)-1)



--ron

 

[Guest]

Hi,

Array enter this one (Ctrl+Shift+Enter)

IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))

A1:A7 houses the SKU no and B1:B7 houses the LoC no. A10 houses the SKU
against which you want the value.

Regards,

Ashish Mathur

 

[Ron Rosenfeld]

Hi,

Array enter this one (Ctrl+Shift+Enter)

IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))

A1:A7 houses the SKU no and B1:B7 houses the LoC no. A10 houses the SKU
against which you want the value.

Regards,

Ashish Mathur

With the data set posted, using your formula, I seem to get the first Loc
returned: A1012


--ron

 

[Ron Rosenfeld]

Hi,

Array enter this one (Ctrl+Shift+Enter)

IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))

A1:A7 houses the SKU no and B1:B7 houses the LoC no. A10 houses the SKU
against which you want the value.

Regards,

Ashish Mathur

OK, I see what's happening here. Your formula needs to be copy/dragged down to
obtain the various instances. That was not initially clear to me.
--ron