Filter formula to search on postcode or suburb

[jessitarexcel]

Hello,

I really need some help breaking down this formula please

I currently have a document that I am working on where I have created a field that allows you to search for the relevant information below by entering in the postcode and the suburb. The screenshot below is a sample of the data type I am working on:

The screenshot below shows the search field - at the moment you need to put in the postcode first and then the suburb. You need to fill in both fields to return a result.



Data Information: There are a number of duplicate postcodes as the postcodes often cover mulitple suburbs. There are also a number of suburbs with the same name.

This is the current formula I have: =FILTER(INDEX(Table13,SEQUENCE(ROWS(Table13)),XMATCH(OFFSET(C6, 0, 0, 1, MAX(FILTER(COLUMN(6:6),(6:6<>""), "")) - MIN(FILTER(COLUMN(6:6),(6:6<>""), "")) + 1),Table13[#Headers])),(Table13[POST CODE]=$D$3)*(($D$4=LEFT(Table13[LOCALITY],LEN($D$4)))))

May I ask for some help so that you can put in either the suburb or the postcode and return the same results?

Any assistance is very sincerely appreciated. I can send a file sample if necessary.

 

[Fluff]

Try

=FILTER(INDEX(Table13,SEQUENCE(ROWS(Table13)),XMATCH(OFFSET(C6, 0, 0, 1, MAX(FILTER(COLUMN(6:6),(6:6<>""), "")) - MIN(FILTER(COLUMN(6:6),(6:6<>""), "")) + 1),Table13[#Headers])),(IF(D3="",1,Table13[POST CODE]=$D$3))*(($D$4=LEFT(Table13[LOCALITY],LEN($D$4)))))

 

[jessitarexcel]

Thank you so much Fluff. I am going to try it now and will let you know how I go.

 

[jessitarexcel]

That was brilliant Fluff, I can see the changes that you have made. I just wasn't sure where to add in the IF part of the formula. It works perfectly now! You have made my day.

 

[Fluff]

You're welcome & thanks for the feedback.