Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Looking up values
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 |
#2
|
|||
|
|||
On 25 Jun 2005 09:20:28 -0700, "gordo" wrote:
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 |
#3
|
|||
|
|||
Hi,
Array enter this one (Ctrl+Shift+Enter) IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$1 0,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 "gordo" wrote: 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 |
#4
|
|||
|
|||
On Sat, 25 Jun 2005 19:44:01 -0700, "Ashish Mathur"
wrote: 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 |
#5
|
|||
|
|||
On Sat, 25 Jun 2005 19:44:01 -0700, "Ashish Mathur"
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero values in a log chart | Charts and Charting in Excel | |||
#N/A Values : Returned by Formulas vs Entered Manually | Charts and Charting in Excel | |||
How To Use Cells Without Values in a Formula | Excel Worksheet Functions | |||
How sum values in column B using values in column A as the conditi | Excel Worksheet Functions | |||
Second serie doesn't use X-as values | Charts and Charting in Excel |