I am fairly new to excel and have a question in regards to an easy way to retrieve data from another table using an array formula.
I have two files that I am using that contain 2 sets of data with columns for name, address, city, and state. The red highlighted data needs be used to find the blue data’s first column, which is a possible name for the company found from the red data. The issue is that the blue data is larger and has rows of data that will not be found in the other table. I have a small sample of the spreadsheet attached.
I attempted to use an if/and statement :
=IF(AND(D2=C8I:I,E2=J:J),H2,0), but found out that it does not work with arrays and only found the first address by default.
How would I set up a formula to retrieve the possible names using criteria from the blue data such as if the address and city match, then input that company name? Or is there a way to format it to try another formula?
Thanks in advance for any help
[attachment deleted by admin to conserve space]
