Forum Discussion
Formula HELP!
=IFERROR(INDEX(Tabelle2!$B$2:$B$27,SMALL(IF($A2=Tabelle2!$A$2:$A$27,ROW(Tabelle2!$A$2:$A$27)),COLUMN(Tabelle2!A:A))-1),"")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. I entered the formula in cell B2 and copied it across range B2:C23.
If you work with Office365 or 2021 you can apply the FILTER function.
OliverScheurich thank you so
much. But I was wondering if for instance contract 1001 had a contract type of both A and B. Is there a way to populate both in one cell? Or
would I have to list each contract number that had more than one type over and over.
In the example of my previous post you can see that e.g. contract number 1002 has two different contract types "B" and "W" which are returned in cells B3 and C3. This means you don't have to enter the contract number over and over if there is more than one contract number.
If you work with Office365 or 2021 you can use the TEXTJOIN and FILTER functions to return all contract types in one cell. The result would look like in this example.
=TEXTJOIN(",",,FILTER($B$2:$B$21,F2=$A$2:$A$21))For further processing it might be better to return every contract type in a single cell though.
Would i be able to
Use a vlookup to have the contract type returned?VLOOKUP only returns the first matching value. There is a workaround with a helper column in addition with VLOOKUP. However INDEX along with SMALL is more suitable if one can't use TEXTJOIN and FILTER (and if the solution should be with formulas).
But there is another option: Power Query. In the attached file you can enter the data in the dynamic table in columns A and B. After this select any cell of the green dynamic table and right-click and refresh.