 # Excel formula with if and vlookup ?

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 ID Characteristic ID Characteristic 1 what formula to enter? 1 x 2 8 x 3 2 y 4 5 y 5 7 y 1 5 6 7 8

19 Replies

# Re: Excel formula with if and vlookup ?

@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.

# Re: Excel formula with if and vlookup ?

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

# Re: Excel formula with if and vlookup ?

@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 Description Group Description Group Apple 0 Apple Fruit Shoe 0 Pair Fruit Phone 0 Potato Vegetable Airplane 0 Cucamber Vegetable Chainsaw 0 Carrot Vegetable Pencil 0 case 2 Description Group Apple 0 Shoe 0 Phone 0 Airplane 0 Chainsaw 0 Apple 0 case 3 Description Group Apple Fruit Shoe 0 Phone 0 Pair Fruit Airplane 0 Chainsaw 0 Pencil 0 case 4 Description Group Apple Fruit Shoe 0 Phone 0 Pair Fruit Airplane 0 Chainsaw 0 Pencil 0 Apple Fruit case 5 Description Group Apple Fruit Shoe 0 Phone 0 Pair Fruit Airplane 0 Chainsaw 0 Pencil 0 Potato Vegetable Pencil 0 Carrot Vegetable Phone 0 Cucamber Vegetable

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

# Re: Excel formula with if and vlookup ?

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".

# Re: Excel formula with if and vlookup ?

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 " & " ?

# Re: Excel formula with if and vlookup ?

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.

# Re: Excel formula with if and vlookup ?

@Quadruple_Pawn 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.

# Re: Excel formula with if and vlookup ?

``````=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.

# Re: Excel formula with if and vlookup ?

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

# Re: Excel formula with if and vlookup ?

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

# Re: Excel formula with if and vlookup ?

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?

# Re: Excel formula with if and vlookup ?

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.

# Re: Excel formula with if and vlookup ?

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

# Re: Excel formula with if and vlookup ?

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") ?

# Re: Excel formula with if and vlookup ?

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.