=IF Formula Question

[Ty_French]

Hi All,

I have figured this out before, but unfortunately lost the data. I am unsure whether I used the IF function before, or a combination of other functions, but after hours of trying I cannot figure it out.

I have a dataset/table on one tab of a sheet (screenshot 1). This table contains columns with data which I want to copy over to another tab (screenshot 2) based on cell values matching. I want the data to be pulled across if A3 matches either columns D or G of the dataset. A3 is populated by a dependant dropdown list.

The problem I am trying to overcome is the formula I am using (below) provides the data, but also provides blank rows if the criteria is not met. In the example shown, the criteria applies to row 3 within the table, and the data pulls the right information over but does it three rows down while including blank rows where the condition is not met.

How can I avoid this, only pulling information over where the criteria is met, and ignoring other entries. As mentioned, I know this is possible, but I am not as familiar with functions such as MATCH or FILTER. I have tried suggestions from other threads but cannot get them to work - any advice would be hugely appreciated!

Thank you very much.

Formula being used:
=IF((A3=Record!D2:D3002)+(A3=Record!G2:G3002),Record!D2:D3002,"")

 

[Fluff]

Hi & welcome to MrExcel.
How about

=filter(Record!D2:K3002,(A3=Record!D2:D3002)+(A3=Record!G2:G3002),"")

 

[Ty_French]

Hello, and thank you for your quick response!

Using this formula unfortunately returned a #SPILL! error.

Any further suggestions would be appreciated. I am looking forward to getting this working again!

 

[Fluff]

Clear all cells to the right of B3

 

[Ty_French]

Yes!!!! Thank you SO much for your help. I did not realise that the cells right of B3 could have interfered with this (clearly!).

I am very grateful for your quick assistance. Thank you again!

 

[Fluff]

You're welcome & thanks for the feedback.