Forum Discussion
Excel formula with if and vlookup ?
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?
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.
thank you very much for you time and instructions.
This will be a great improvement for me.
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.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") ?