Using if() and and() to search a range of cells

[SRZimm]

Hi, all. I'm new to the site but have used used responses in threads as help for awhile now. I'm moderately Excel proficient. There's an action I'd like Excel to do for me, which seems simple enough in concept, but for the life of me I can't figure out the formula.

I have two sheets with about a quarter million rows of data each. The first sheet is industry relevant info relating to specific firms from year 1; the second is data from year 2. I'd like to combine the data into one spreadsheet so I could analyze aggregate data. The firms listed in the sheets are close to the same but are NOT identical (some firms were removed/added between the years), so I can't simply sort and then copy and paste as the rows wouldn't match. There is a unique identifier "Provider ID" column in each sheet so in a sense synthesizing the two sheets is completely do-able.

Now, here's where it gets challenging (to me at least). There are about 40 rows of info for each firm in the sheets (the Provider ID is listed on all 40 rows so, again, it is easy to connect any data to specific firms).

I want to put a function on sheet 1 that will search sheet 2 for rows having both: (1) a Provider ID (in column A) matching a given Provider ID from sheet 1 and (2) certain text in column B of Sheet 2; and when those two conditions are met, I want to pull the data from column C. If those conditions aren't met, then it could just return a "" value. Finally, I can't use a simple SUMIFS command b/c the data in column C is sometimes "Not Available", and if I use sumifs, the "Not Available" cells return a value of 0. This won't work b/c there is meaningful difference between 0 and "Not Available" for purposes of aggregating the data, so I need to be able to distinguish b/w those two results. Otherwise, SUMIFS would work and this would be easy.

Essentially, I want to have a formula that does the following (I'm going to put it into a hypothetical Excel command, even though what I'm writing doesn't work): =if(and(Sheet2!$A:$A=B2,Sheet2!$B:$B="certain text"),Sheet2!$C:$C,"").

I know this is a LONG question, and I apologize for the length. I wouldn't greatly appreciate ANY help anyone can give. I'm at a loss at the moment, even though this would seem to be something very easy for Excel to handle. Thank you in advance!

[BillyRaySpivy]

It would be helpful to attach a simplified file with a few data sets. Once the formulas are in place you can copy down the ranges. Put as much detail as you can to help someone here to get the results you need.

[SRZimm]

I'm sorry but I don't know how to attach a file to a message . . .

[BillyRaySpivy]

When replying click on 'Go Advanced'. It will open another window to select which file to upload. Once you have done that close that window and continue with replying.

[sandy666]

Attach a sample workbook. (Not a picture!)
Make sure there is just enough data to demonstrate your need. Include a BEFORE (original) sheet and an AFTER (required output) sheet in the workbook if needed to show the process you're trying to complete or automate.
Make sure your desired results are shown, mock them up manually if necessary. Remember, it should reflect original structure of your data.
Remember to desensitize the data.
Note:

• Please do not attach password protected workbooks/worksheets
• Please do not attach file(s) from exterior servers
• Please do not attach file(s) with enabled any Workbook Open/Autorun macros!

20 rows of data is enough (probably)

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
If link is not from FORUM server you have to wait until someone will want to watch the file from an external server.

[BillyRaySpivy]

Yes do what Sandy mentioned

[SRZimm]

OK. Thanks. I think I just uploaded a dummy file. There are two worksheets. I want to pull the score from sheet 1 and put it into the yellow row in sheet 2 where the provider ID and the info in column B match up. Thanks!

[SRZimm]

I was working on creating the dummy data and uploading it when Sandy's post posted. (Thanks, Sandy.) So, I may not have followed those instructions exactly. I think the gist of what I am asking is clear. If not, let me know. Thanks for any help!

[sandy666]

try

Please Login or Register  to view this content.

confirm by Control+Shift+Enter. If not formula will not work.

[SRZimm]

The function returns #VALUE!, even when I press control+shift+enter. I'm wondering if there is a tweak to the control+shift+enter for a mac?

[sandy666]

try:

It looks like the key combination for entering the array formula has changed from ⌘ + RETURN to CTRL + SHIFT + RETURN

btw. Google search: (keywords) control shift enter for mac

[BillyRaySpivy]

Okay I think I have what you need:

Formula in G2:

Please Login or Register  to view this content.

Press CTRL+SHIFT+ENTER to make it an array. Then drag it down to the end of your column.

I attached the updated file to show this.

Good luck with your project.

[Phuocam]

Try this ...

=INDEX(sheet1!$C$2:$C$250,MATCH(sheet2!A2&sheet2!B2,INDEX(sheet1!$A$2:$A$250&sheet1!$B$2:$B$250,0),0))

Or:

=LOOKUP(2,1/(sheet1!$A$2:$A$250=A2)/(sheet1!$B$2:$B$250=B2),sheet1!$C$2:$C$250)

Normal Enter.

[SRZimm]

So, what you're saying is "google is my friend". I had found that already actually and tried CTRL + SHIFT + RETURN, but it is not working.

You've gotten me well down the road - I see what you did with that formula and it makes perfect sense - and will make my life easier. I just need to figure out how to make it work, I think. Thanks so much. Looks like I need to familiarize myself with "array formulas".

[BillyRaySpivy]

Check my reply with the updated file it works!

[sandy666]

You can use Phuocam's formula without Control+Shift+Enter if it will be easier for you

[SRZimm]

You all are Excel rock stars. Wow. Sandy, Billy Ray and Phuocam -- thank you!

[sandy666]

If the problem is solved
- it's always a good practice to show respect to the person(s) who have helped you
- is a click on the Add Reputation first and then
- mark the thread as SOLVED,
Thanks

[SRZimm]

I will do those things. Thanks, Sandy.

[sandy666]

You are welcome
Sorry for the Cotrol Shift Enter but I am not a happy Mac user