CODEWITHSUNDEEP
excelexcel-2010
nojohnny101
nojohnny101

Reputation: 562

excel 2010, look up blank cells in two columns, return text in corresponding different column

Here is a screenshot of my data:

Data

I want to search B4:B40 and C4:C40 and find all cells that are blank for both specified ranges. If blank in both ranges, return the text in the corresponding row in column A. Ideally, the output could be combined and it could list something like 8:00-2:00.

here is what i have so far: =INDEX('C:\Path\to\other\worksheet\[Blank Schedule Grid.xlsx]Susan'!B4:B40, MATCH("",'C:\Path\to\other\worksheet\[Blank Schedule Grid.xlsx]Susan'!B4:B40,0),'C:\Path\to\other\worksheet\[Blank Schedule Grid.xlsx]Susan'!B2)

Am I on the right path?

Upvotes: 0

Views: 49

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

The formula you want is:

=IFERROR(INDEX($A$18:$A$40,AGGREGATE(15,6,(ROW($A$18:$A$40)-ROW($A$18)+1)/(($B$18:$B$40="")*($C$18:$C$40="")),ROW(1:1))),"")

I will let you put in the workbook and sheet references. They are only needed on the Ranges. Do not put them on the Range references in the the ROW() functions. They are only there to return a number and it does not matter the sheet to which they reference. Just make sure that the ranges themselves match.

enter image description here

The concatenation with the workbook references would make the formula long and unwieldy. To do that VBA would be the best route.

Edit:

Got bored and decide to put in your workbook and worksheet references:

=IFERROR(INDEX('C:\Path\to\other\worksheet\[Blank Schedule Grid.xlsx]Susan'!$A$4:$A$40,AGGREGATE(15,6,(ROW($A$4:$A$40)-ROW($A$4)+1)/(('C:\Path\to\other\worksheet\[Blank Schedule Grid.xlsx]Susan'!$B$4:$B$40="")*('C:\Path\to\other\worksheet\[Blank Schedule Grid.xlsx]Susan'!$C$4:$C$40="")),ROW(1:1))),"")

Upvotes: 2

Tim Wilkinson
Tim Wilkinson

Reputation: 3801

Do you want all the values in a single cell or in a new column?

=IF(AND(ISBLANK(B18), ISBLANK(C18)), A18, "")

Assuming your data starts in A18 (as per the screenshot), you can put this in cell D18 and copy all the way down.

Upvotes: 0

Related Questions