Forum Discussion
Formula HELP!
Hi! I have an excel question. I have a workbook that has several sheets. My main sheet(sheet 1) is what I’m trying to place a formula in. I want cells in column B to auto fill with data from sheet 2 IF column A in sheet 1 matches column A in sheet 2. However, it’s possible that there are more than one matching column A.
so column A has contract numbers in sheet 1.
Sheet 2 has
column A - contract numbers
Column B - contract types
it is possible that one contract can hVe 2 different type so I want both types to auto fill on sheet 1
5 Replies
=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.
