Hello,
Been awhile since I asked a question of a problem!
In my workbook in TAB ( S )
in Cell F4 I have =IFERROR(INDEX($B$4:$B$20, SMALL(IF($E4=$A$4:$A$20, ROW($B$4:$B$20)-2,""), COLUMN()-5)),"")
I am looking up an INDEX with a SKU Column A number that can provide multiple locations in a row from column B. I was provided with this formula which seems to work in text but not from numerical.
Do I have to use SMALL??? function or im a doing it wrong here or is there any other way to index various locations from numerical SKU'S????
I have been looking at this for the last 4 days and its now bothering me!!
Thanks
Steve behr
Could you upload a file demonstration your issue please? And which Excel version are you using?
Please see attached spradsheet
Steve
And which version of Excel do you have?
I have bisiness 365
Steve
Hi,
I can confirm that I have Microsoft 365 version 2308
Thanks
Steve
Great! Though v2308 is rather old. I'd recommend to update first.
From the file I noticed that you have used pre-365 array formulas entered with Ctrl-Shift-Enter. That will not be necessary in modern Excel. Furthermore, Excel 365 has several new dynamic array functions that you could use.
Perhaps the attached file contains a solution that works for you.
Thanks Riny,
However couple of things.
Will this work in 365, because at home i have 2019 ver 2109.
When input a new number 192282800044 in cell A29 the data from E-G has #name?.
I really cant upgradd work laptop at work unless i pay for it!.
I like the idea of your formula however i thought you wanted me to stay away from arrays?
Let me know if this work on 365 and ill get bavk to you.
Thanks
Steve
This works on 365 but not on 2019.
With regard to the number 192282800044, I noticed that all your 'numbers' in column A are in fact texts, except for the afore-mentioned number on row 17. You have to be consistent here. So either all texts that look like numbers or all real numbers. In the file that I attached earlier, I change the real number in A17 to a text.
Regarding the Name error, that's in Excel 2019, I presume. If not, please upload the file with the error.
Hi Riny,
Thanks for that! it works well with 365 on work laptop....however!
I have on my short pick program where the sheet is connected to a data source (Powery query) to a table.
SKU Location
| 0000198732 | DTS | 0009112595 | 18788300040 | 300006821655 | 1 | #N/A | #SPILL! | |||||
| 0000198732 | DTS | 0009112596 | 300006821655 | 1 | #N/A | #SPILL! | ||||||
| 0000198733 | DTS | 0009112597 | 9310000529755 | 1 | #N/A | #SPILL! |
As I used 2 scenarios , one with inside the data source (As above result....#SPILL!), I presume that this not the way to go as its information. 2nd scenario is that I can have this feature outside the data source.
Question is, could have this formula in power query taking the information of the master SKU/Location list from a specific named sheet?
In anycase, the answer to the previous question was answered!
Thanks
Steve
