Using a Match formula

[mikydred]

Can you please help me, I want to create two input options to search a report (by zip code & City). I have the formula to match the zip code that is typed-in which is =MATCH($D$1,'Zip Export'!F:F,). How do I include in this formula to also match city that is typed in.


Thank you.

 

[jeremymjp]

It's an array formula, so you'll need to enter it into a cell and press ctrl+shift+enter:


=INDEX(ReportRange,MAXA((ZipRange=ZipEntry)*(CityRange=CityEntry)),1)


If you have multiple reports within a single zip/city combo, I don't believe this will work. But, if there's just one report that matches a zip and city combo, you're good to go.


Someone else may weigh in for a formula that works either condition...

 

[mikydred]

Thanks jeremymjp.

 

[Jack]

Here you go if I understand what you want the row no of the match - entered as an array that handles if there isn't a valid combiation of City and Zip.


{=IF(ISERROR(MATCH(CityEntry&ZipENtry,CityRange&ZipRange,)=TRUE),"No Match",MATCH(CityEntry&ZipENtry,CityRange&ZipRange,))}


This will concatenate City and Zip in the array and test the combination matches the combination of city and zip in the list and if there is somethig wrong with the entry then gives "No Match". Maybe this is an alternative to Jeremy's I just didn't have time to play with it.


THanks