How to list all values in column B that have a lower value in column E than the connected value in column C
This proved to be extremely difficult to explain in the title, so apologies for the cryptic header.
I'm essentially trying to find a formula that gives me the same as in the "Goal" array from the "Inventory Count" array. My plan is to find a formula that gives me the values from the B (Item) column, only if the matching value in the E (current) column, is less than the matching value in the C (min) column. I will then use Xlookup to find the inventory value, and xlookup-inventory to figure out how much to produce to end up at the maximum value (See column M (needed).)
I've tried using a sort(filter()) formula, but this only returns "failed" (failed is my own word I make it say when it isn't able to complete the request), it also only returns it in one cell. The final part for the sort part of the formula is to be able to have it sorted from smallest to largest in the "needed" column, but this might need a few workarounds, so my main focus is just to have it return the neccesarry Item values in any order.
Below is another attempt of mine, here I was able to have it return a full list, but it still only returns "failed"
This is a slightly simplified sheet from the real thing. In the actual sheet I have other things below the "inventory Count" array, in the same columns, so using the entire column as a ref will not be possible. The values is column E (current) are also the result of a formula, and is subject to change.
Thanks for any help!
EDIT:
Thanks so much for all the response! Especially everyone who tested my formula for themselves and showed me that it worked!
It turns out that I wrongly assumed the problem was somewhere in the formula itsself. In fact the problem turned out to be that Excel didn't read the numbers in the "Current" column as numbers, even after I changed them from general to numbers. I ended up figuring this out by attempting to add a decimal to all the numbers in the sheet, and the ones in the "current" column didn't change.
The problem was in the formula I used to get the numbers for the "current" column from a different array.
=IFNA(TEXTAFTER(INDEX(BO:BO,MATCH(BQ5,BO:BO,0)+1,1),"x "),"")
The solution was as simple as just adding a =NUMBERVALUE() around the entire formula. In the end this is the final product!
[link] [comments]
Want to read more?
Check out the full article on the original site