Excel formula with if and vlookup ?

Copper Contributor

First of hello Community!

 

I am using :

Microsoft® Excel® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20272) 64-bit

 

I need VLOOKUP and IF formula with more conditions, maybe even some other formula:

I have two tables:

First table is with list of data of numbers.

Second Table where these numbers are grouped by same characteristics.

 

I need some kind of IF and VLOOKUP (or something else) to:

1. If only one ID with characteristic is in first table don't return data.

2. If only one ID with characteristic is repeating don't return data.

3. if there are two or more ID with same Characteristic, then return Characteristic in first table behind each ID.

 

list of data  Characteristic per data   
IDCharacteristic IDCharacteristic  
1 what formula to enter? x  
2  x  
3  y  
4  y  
5  y  
1      
5      
6      
7      
8      

 

thanks in advance!

 

19 Replies
If I read it correctly, 1 and 2 contradict each other?

@Jan Karel Pieterse yes, they are not the same value but unique: "1" has its specifications but is under same characteristic "X" and "8" has its specifications different from "8", but is under same characteristic "x" as 1.

I'm sorry, I have no idea what you mean :(

@Jan Karel Pieterse  please see my new table where you can better understand logic and how it should work:

Case tables are what are possibilities that i can have in my sheets, and to the right is fixed table from which there can be readings. 

 

       
 case 1  Table  
 DescriptionGroup DescriptionGroup 
 Apple0 AppleFruit 
 Shoe0 PairFruit 
 Phone0 PotatoVegetable 
 Airplane0 CucamberVegetable 
 Chainsaw0 CarrotVegetable 
 Pencil0    
       
 case 2     
 DescriptionGroup    
 Apple0    
 Shoe0    
 Phone0    
 Airplane0    
 Chainsaw0    
 Apple0    
       
 case 3     
 DescriptionGroup    
 AppleFruit    
 Shoe0    
 Phone0    
 PairFruit    
 Airplane0    
 Chainsaw0    
 Pencil0    
       
 case 4     
 DescriptionGroup    
 AppleFruit    
 Shoe0    
 Phone0    
 PairFruit    
 Airplane0    
 Chainsaw0    
 Pencil0    
 AppleFruit    
       
 case 5     
 DescriptionGroup    
 AppleFruit    
 Shoe0    
 Phone0    
 PairFruit    
 Airplane0    
 Chainsaw0    
 Pencil0    
 PotatoVegetable    
 Pencil0    
 CarrotVegetable    
 Phone0    
 CucamberVegetable    
       

 

case 1 - if there is Apple from table but not Pair in table, return 0.

case 2 - if there is Apple repeating but not Pair in table, return 0.

case 3 - in table is Apple and Pair, table should return Fruit

case 4 - in table is Apple repeating but also in table is Pair, table should return Fruit

case 5 - in table is Apple repeating but also in table is Pair, table should return Fruit, but in Table is also other group with same rules, and it returns for Potato, Carrot and Cucumber - Vegetable

@Dusandza 

The formula in the attached file seems to do what you are looking for. Actually it's two times the same formula connected by the " & " operator. The part of the formula before " & " checks for "Fruit" and the other checks for "Vegetable".

 

Thank you very much!
I actually have a big table with around 700 characteristic per different fruits.
do I need to enter for each one formula and " & " ?

@Dusandza 

You are welcome. If you have "Fruit 1", "Fruit 2" ..... "Fruit 700" instead of only "Fruit" and "Vegetable" you can apply the formula in the attached file unless you want to see all results at the same time. In the attached file in cells G1 and H1 you can dynamically enter 2 characteristic to return their results.

 

You can as well combine a formula with e.g. ten times " & " in order to dynamically return 10 results at the same time. The maximum number of characters in a cell in Excel is 32767. Therefore it shouldn't be possible to combine more than roughly 70 fruits in a formula.

@OliverScheurich Can you post this as an Excel file attachment rather than a PDF please? The German formula makes it very hard for international users to use the solution.

@Jan Karel Pieterse 

=IF(AND(COUNTA(UNIQUE(FILTER($D$3:$D$11,$E$3:$E$11=G1)))>1,
COUNTA(UNIQUE(MMULT(--($A$3:$A$12=TRANSPOSE(UNIQUE(FILTER($D$3:$D$11,$E$3:$E$11=G1)))),
SEQUENCE(COUNTA(UNIQUE(FILTER($D$3:$D$11,$E$3:$E$11=G1))),1,1,1))))=
COUNTA(UNIQUE(FILTER($D$3:$D$11,$E$3:$E$11=G1)))+1),
IF(MMULT(--($A$3:$A$12=TRANSPOSE(UNIQUE(FILTER($D$3:$D$11,$E$3:$E$11=G1)))),
SEQUENCE(COUNTA(UNIQUE(FILTER($D$3:$D$11,$E$3:$E$11=G1))),1,1,0))>0,G1,"")
,"")

This is the translation of the suggested formula.

 

I'd prefer to attach the Excel file which is no problem with Excel 2013 files. However the above formula is from Excel online (i don't work with Office365 or 2021) and i haven't found a way to attach the online file to my reply.

@OliverScheurich 

You can download the workbook from Excel Online to your computer, even if you don't have the latest version of Excel. You can then upload it.

Or you can give other users read-only access to your online workbook.

@Hans Vogelaar 

Does it work when you open the attached file? I get many error messages maybe because of my older version of Excel.

@OliverScheurich 

It works, thank you

image.png

Just in case, you may install Advanced Formula Environment add-in for browser version as well.

Hi,
its working on my laptop, buuut its not doing totally what i want.
Each week i receive a different set of "fruits" in column A (ID column). I need to be able to copy formula downwards.
On the other hand i have a big sheet with 764 ID, with are grouped in 338 groups (characteristic column) and the list is growing. Why do i need G, H and I column?

@Dusandza 

The formula in the attached file is adapted to ranges $A$3:$A$1000, $D$3:$D$1000 and $E$3:$E$1000. You can enter additional data into these ranges and the formula spills the results in range $B$3:$B$1000. This means that you don't have to copy the formula down. You can as well adapt the ranges to e.g. $A$3:$A$20000, $D$3:$D$20000 and $E$3:$E$20000.

 

In cells G1, H1 and I1 you can dynamically enter the characteristics from column E for which you want to see the results.

@Dusandza 

Sorry, I only confirmed to @OliverScheurich  what attached file is opened correctly. Didn't dig the content.

@OliverScheurich 

This is working, but still not 100% to what i need:
So, I receive list of ID. Then i need to copy ID into Mappe 33 for check up, but formula returns only 3 values, that are set in G1, H1 and I1. How do I overcome this "limit" (G1, H1 and I1) if i have 338+ groups ("Characteristic") ? 

@Dusandza 

The maximum number of characters (digits) in a cell in Excel is 32767. One formula has already about 450 characters (digits). Therefore it shouldn't be possible to combine more than roughly 70 formulas with " & " into one comprehensive formula.

 

If you have 338 characteristic from column E you can use 5 result columns (instead of only column B) with one formula each. Each formula then checks for roughly 70 characteristic.
 
For one comprehensive formula you can then dynamically enter the characterisitc from column E in e.g. range G1:BX1 (70 cells with 70 characteristic). Of course you can as well enter the criteria for example in range G1:P7 which i would prefer. For the next comprehensive formula you can then enter the criteria in range G8:P14 and so on.

@Sergei Baklan 

Thank you for your feedback and the reference to the Advanced Formula Environment add-in.

@OliverScheurich 

thank you very much for you time and instructions.

This will be a great improvement for me.