You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
Good morning hive mind. My brain isn’t working at the moment and I need help building a simple formula.
Basically I have two columns, B and C. I need to populate a buy and sell price in columns E and F respectively if B contains certain text and C equals Y or Z. I figured IFS would do this but it’s not working.
So for example if column B contains Bike and column C is MTB or Gravel, E equals 1 and F equals 2.
No idea why I can’t do this but think I may be over thinking things. Help me STW
(e1) =if(and(b1="bike", or(c1="mtb", c1="gravel")), 1,"mamil")
Should work I think.
=IF(ISNUMBER(SEARCH("abc",A1)),"Text if yes","Text if no")
Is a basic formula to fill a column based on text search, easy solution if you're just looking for one string. If it's multiple strings then a reference table and lookups.
IF(AND(conditions) If you need both columns to match, I wasn't clear on that from your question.
I'd be tempted to use some more columns... over to the right and/or hide if you want - and you can put some booleans in there.. so in T2 (or something), have =upper(B2)="BIKE"
U has equivalent with =or(...) then you can just do e2 with =if(and(t2,u2), "1","")
Same as @dangerourbrain but put the checks in their own cells and it's a lot easier to see why it's not working and/or expand.
Thanks all - I’ll add a table and incorporate as part of a VLOOKUP
As someone who will happily nest enough logics to ensure cells are completely incomprehensible 30 seconds after you typed the formula, even I would shy away from multiple vlookups combined with logics.
If it looks like combined tables, logics and vlookups are the way forward a pivot table is often a better option
Import the data into Powerbi, have found it is so much easier to work with big data sets
I know the problem has already been solved, but for this sort of thing then I do as @euain suggested which is to add extra columns so you can see what is going on. It's super easy to write big formulas that look as though they are working but in reality they are churning out junk. Adding extra columns makes it easier to see if that is happening
also vlookups are outdated- its xlookups now
Not well versed in PowerBI so that’s adding a layer of unwanted complexity to proceedings.
I like nested index match functions more than vlookup. Vlookup needs data to be sorted, I think.
also vlookups are outdated- its xlookups now
Only if you're not interested in backward compatibility.
Vlookup needs data to be sorted, I think.
Only if you want to use the inexact lookup (TRUE at the end) which hardly anyone ever does, and it gives terrible results on unsorted data so why the hell did they make it the default?
Not well versed in PowerBI so that’s adding a layer of unwanted complexity to proceedings.
TBH even if you are well versed in Power BI then DAX can still add a layer of unwanted complexity to proceedings!
I like nested index match functions more than vlookup. Vlookup needs data to be sorted, I think.
It doesn't - but you need to use the "false" argument at the end. It does however need the lookup term to be leftmost in the region you're looking at.
XLOOKUP is a more interesting looking function. Removes most of the complaints about VLOOKUP.
We use index/match for performance reasons when using spreadsheets in anger though - but they can be big and often not being run through Excel itself but other spreadsheet engines that use Excel files.
Used VLOOKUP with a table for ref with IF/OR
All sorted