CODEWITHSUNDEEP
excelfunctiongoogle-sheetsexcel-formula
Vesuvius
Vesuvius

Reputation: 3

Pull cell data while other cells within row match a string

I'm using google sheets for a work project, and I'm trying to pull data from a cell that is in a row with 2 cells that match what I'm looking for.

For example: I have 8 columns and 5600 rows in sheet 2. I want to pull the data from column "D" but only if the data in columns "A" & "C" match what I'm looking for. Say column "A" is filled with days of the week, and column "C" is filled with first names, and column "D" is filled with an ID number. I want the ID number from column "D", but only from the rows in which column "A" = "Thursday" & column "C" = "Jeff". There will also be some rows that share columns "A" & "C", but have a different number in column "D", and I would like to pull those as well.

Here is my attempt where I was able to grab the day of the week:

=INDEX('sheet2'!A:A,MATCH((INDIRECT("RC[-5]",0)),'sheet2'!C:C,0),)

(the indirect function is there because the first name I want to match for is always going to be 5 cells to the left of where this function is)

And here is my attempt where I was able to grab the ID number:

=INDEX('sheet2'!D:D,MATCH((INDIRECT("RC[-5]",0)),'sheet2'!C:C,0),)

So, I want to be able to call the second function above, but only if the first function evaluates to "Thursday", for example.

Upvotes: 0

Views: 628

Answers (1)

thomasl
thomasl

Reputation: 191

To me this boils down to two different problems

Q1) A multiple criteria lookup (INDEX+MATCH)

Q2) Returning several values when multiple rows match your criteria

Assume that the weekday is 6 cells to the left of your function and the first name 5 cells to the left. I have also restricted the range from searching the whole column. Adjust as needed, but the complete column is a bit too much for Excel to handle.

A1) This simple lookup will deal with the multiple critera issue, but doesn't fetch duplicates of the name and weekday combination.

 =INDEX(sheet2!D2:D12,MATCH((INDIRECT("RC[-6]",0)&INDIRECT("RC[-5]",0)),sheet2!A2:A12&sheet2!C2:C12,0))

A2) This formula would list all the results in the same cells delimited by a comma, looking like this "121208,445555,445553", not on separate rows.

=TEXTJOIN(",",TRUE,(IF((INDIRECT("RC[-6]",0)=sheet2!$A$2:$A$12)*(INDIRECT("RC[-5]",0)=sheet2!$C$2:$C$12)*(1-ISBLANK(sheet2!$D$2:$D$12)),sheet2!$D$2:$D$12,"")))

Edit after comment feedback: Some versions of excel requires you to apply this formula with Ctrl+Shift+Enter instead of just Enter for it to work properly.

If you need to have all the matches for a pair of criterias stacked on separate rows I would recommmend that you check out the Power Query tool (native in the latest versions of Excel). This would be a relatively easy task for PQ to handle by merging tables with join kind "Left Outer".

Upvotes: 1

Related Questions