Forum Discussion
LEFT formula based on adjacent cell text and pertaining value
Hi,
i have a report which consists of thousands of entries in column A and B only (column C and D do not exist in the report).
The example below shows all the categories in Column B within the entire report.
i need to create a formula (in D2 for example) that will take A2 value ,identify the category next to it in Column B and perform the =LEFT formula (based on the value i have entered in column c).
Column C will not exist in the report therefore im assuming a single formula can be created where it lists the all the categories and assigns the appropriate value within the formula to perform the =Left function with?
something like: if "100 Jeans"=6, if "105 Jeans Kids"=6,if "110 Pants"6,if "120 Denim Jackets"=10 etc etc
i hope this makes sense, apologies for the poor explanation - basically i need to create column D with a formula and Column C will not exist yet these are the correct values per category i need to use.
thanks
Dan,
I've understood from your explanation that you want to extract from column A a text with a set of characters based on a category:
- 6 characters with the categories: 100 Jeans, 105 Jeans Kids, 110 Pants.
- 10 characters with the others.
If so, you don't have to create a helper column (column c), you can identify the set of characters inside the formula in cell D2 as follows:
=LEFT(A2,IF(OR(B2="100 Jeans",B2="105 Jeans Kids",B2="110 Pants"),6,10))
Hope that helps.
Dan,
I've understood from your explanation that you want to extract from column A a text with a set of characters based on a category:
- 6 characters with the categories: 100 Jeans, 105 Jeans Kids, 110 Pants.
- 10 characters with the others.
If so, you don't have to create a helper column (column c), you can identify the set of characters inside the formula in cell D2 as follows:
=LEFT(A2,IF(OR(B2="100 Jeans",B2="105 Jeans Kids",B2="110 Pants"),6,10))
Hope that helps.
Cheers Haytham,
This method works perfectly (especially if new categories are added in the future where they will simply default to "10" characters, which is what i want) - thanks
Hi Dan,
You'll need a formula like =LEFT(A2,C2). But if you don't want to use column C, where are the numbers 6 and 10 based upon?
You could use =LEFT(A2,IF(B2="100 Jeans",6,10)), but then you'll need a long formula if you want to include all criteria.
And why not just hide column C?
Kr
TK
Hi Tinn,
Column C was simply added/used as a reference for my query here, it doesn't exist in the report i pull therefore cannot be hidden.
thanks,
Dan
Hello Dan,
Please open the attached workbook.
I have put the formula there. So, you do not need to have the helper column of C.
here is the formula. So all you have to change is the A2 and B2 in the below formula, in case your actual data are in different columns.
=LEFT(A2,VLOOKUP(B2,{"100 Jeans","6";"105 Jeans Kids","6";"110 Pants","6";"120 Denim Jackets","10";"130 Jackets","10";"140 T-shIrts","10";"150 Shirts","10";"160 Sweatshirts","10";"170 Knits","10";"180 Accessories","10";"190 Underwear","10";"990 Miscellaneous","10"},2,0))
