Return text from cell in column containing text, as long as values in other columns match several criteria

[LordClick]

How do I return the text from the only cell in column C as long as the values in columns A and B also match certain criteria?

A B C
1 Title 1 Type 1
2 Title 1 Type 1 Hello
3 Title 1 Type 1
4 Title 1 Type 2
5 Title 1 Type 2 World
6 Title 1 Type 2
7 Title 2 Type 1 It's
8 Title 2 Type 1
9 Title 2 Type 1 Me
etc.

E.g. in the above table I would like to, of all Title 1 and Type 1 rows, return the text in column C (on any of the rows) - in this case "Hello". How do I do that? In case it matters, there may be other non-relevant columns between A B and C.

As a second step, if there is more than one cell in column C containing text, I would like to return all of those cells and concatenate the text they contain. E.g. in the above table I would like to, of all Title 2 and Type 1 rows, return the text in column C on any of the rows - in this case "It's Me" (or "Me It's", the order doesn't matter). Is that possible?

 

[etaf]

if you have the FILTER() function - 2021 version or 365 version of excel
=FILTER(A1:C9,(A1:A9="title 1")*(B1:B9="type 1")*(C1:C9<>""))

it would be worth updatign your profile with excel version

Book2
	A	B	C	D	E	F	G
1	Title 1	Type 1			Title 1	Type 1	Hello
2	Title 1	Type 1	Hello
3	Title 1	Type 1
4	Title 1	Type 2
5	Title 1	Type 2	World
6	Title 1	Type 2
7	Title 2	Type 1	It's
8	Title 2	Type 1
9	Title 2	Type 1	Me
Sheet1

Cell Formulas
Range	Formula
E1:G1	E1	=FILTER(A1:C9,(A1:A9="title 1")*(B1:B9="type 1")*(C1:C9<>""))
Dynamic array formulas.

and to join
=TEXTJOIN(" ",,FILTER(C1:C9,(A1:A9="title 2")*(B1:B9="type 1")*(C1:C9<>"")))

Book2
	A	B	C	D	E	F	G	H	I
1	Title 1	Type 1			Title 1	Type 1	Hello		It's Me
2	Title 1	Type 1	Hello
3	Title 1	Type 1
4	Title 1	Type 2
5	Title 1	Type 2	World
6	Title 1	Type 2
7	Title 2	Type 1	It's
8	Title 2	Type 1
9	Title 2	Type 1	Me
Sheet1

Cell Formulas
Range	Formula
E1:G1	E1	=FILTER(A1:C9,(A1:A9="title 1")*(B1:B9="type 1")*(C1:C9<>""))
I1	I1	=TEXTJOIN(" ",,FILTER(C1:C9,(A1:A9="title 2")*(B1:B9="type 1")*(C1:C9<>"")))
Dynamic array formulas.

 

[LordClick]

Wayne, oh my god, this works perfectly - thank you so much also for the super quick reply!!! I do have 365, I will update that.

I tried various ways but would never have figured this out...

Is there also a way to insert a character or characters between the joined texts (but only if there are indeed multiple texts? E.g. a line break (CR) or a string like " - "?

 

[etaf]

=TEXTJOIN(" ",,FILTER(C1:C9,(A1:A9="title 2")*(B1:B9="type 1")*(C1:C9<>"")))
the " "
is the delimiter
so that can be changed to
=TEXTJOIN(" - ",,FILTER(C1:C9,(A1:A9="title 2")*(B1:B9="type 1")*(C1:C9<>"")))
a line break is CHAR(10)
let me look at that
EDIT
=TEXTJOIN(CHAR(10),1,FILTER(C1:C9,(A1:A9="title 2")*(B1:B9="type 1")*(C1:C9<>"")))
make sure wordwrap is not off

Book2
	A	B	C	D	E	F	G	H	I
1	Title 1	Type 1			Title 1	Type 1	Hello		It's Me
2	Title 1	Type 1	Hello
3	Title 1	Type 1
4	Title 1	Type 2
5	Title 1	Type 2	World
6	Title 1	Type 2
7	Title 2	Type 1	It's
8	Title 2	Type 1
9	Title 2	Type 1	Me
10
Sheet1

Cell Formulas
Range	Formula
E1:G1	E1	=FILTER(A1:C9,(A1:A9="title 1")*(B1:B9="type 1")*(C1:C9<>""))
I1	I1	=TEXTJOIN(CHAR(10),1,FILTER(C1:C9,(A1:A9="title 2")*(B1:B9="type 1")*(C1:C9<>"")))
Dynamic array formulas.

 

[LordClick]

I see. That works well, thanks!

Now the problem I have is that I would need to pull data in this way across a large number of cells (1000s). It looks like this approach uses way too much computing power, as calculation slows down dramatically even when I copy the formula to only 30 cells or so. Is there another way to pull the same information that is not that resource intensive?

 

[etaf]

i suspect maybe you need Power Query or VBA , which i do not provide answers to here - sorry
how many 1000's i have used with quite a few 1000 rows

 

[LordClick]

Got it, thanks. Looks like it would be a lot more cells eventually - like 1.8m cells Gotta find another approach.

Maybe the following question will lead to another solution: Do you know how to determine available date ranges in a given date range based on a number of unavailable date ranges during that date range? E.g.:

Date range: 2/24/2024 - 12/31/9999
Unavailable dates: 4/1/2024 - 5/31/2024, 9/1/2024 - 10/31/2024, 1/1/2025 - 3/31/2025
The result I would be looking for are the date ranges 2/24/2024 - 3/31/2024, 6/1/2024 - 8/31/2024, 11/1/2024 - 12/31/2024 and 4/1/2025 - 12/31/9999...

 

[etaf]

no sorry not sure what you are doing there exactly
or how to solve - looking for missing dates and then using as a range

for the PQ or VBA - i suggest you start a new thread - BUT put in the title VBA or PQ , and specify that a function is NOT required because of speed, and PQ or VBA is needed also mention the number of rows and that the spreadsheet is SLOW, using a TEXTJOIN() and FILTER() function

There is a forum called Power Tools - maybe worth posting in that forum - and you should link back to this thread

Return text from cell in column containing text, as long as values in other columns match several criteria

How do I return the text from the only cell in column C as long as the values in columns A and B also match certain criteria? A B C 1 Title 1 Type 1 2 Title 1 Type 1 Hello 3 Title 1 Type 1 4 Title 1 Type 2 5...

www.mrexcel.com

use XL2BB or a share to show the expected results - as thats better then text or a image

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone