mirroring a range of cells from a table based on criteria?

JeffGalbraith

New Member
Joined
Apr 9, 2023
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
Is it possible to have a cell or a range of cells dynamically report the contents of a range of cells from another sheet based on a criteria? ie: SUMIF works great for finding cells in a table that are adjacent to cells matching a specified value, but that's only useful if they are numerical values and you want them added up. I'm workmen with text strings and want the listed out one by one...

I tried using TEXTJOIN such as:
=TEXTJOIN(CHAR(10),TRUE,SKILLS!C27:C35)

But in place of the bolded range C27:C34 shown above, what I really want is the formula to search through a large dataset and find the values adjacent to cells that match the contents of a specific cell. I tried writing the range in this TEXTJOIN formula as shown below by replacing the explicit cell range with a VLOOKUP function, but it just returns a #REF! output.
=TEXTJOIN(CHAR(10),TRUE,VLOOKUP(B5,SKILLS!B:B,SKILLS!C:C,TRUE))

I should point out that this option of using TEXTJOIN wouldn't' be my first choice because it will lump / concatenate them all together into a single cell, but if it's the only thing that works I'll settle for it.

Ideally the formula would be structured to that if it found 7 cells in the dataset that match the criteria, each of those 7 values would be entered into a column of 7 cells on the desired worksheet.

Are either of these options possible with excel? Or do I need to hire a programmer? :)
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
I found some other threads that showed how to use FILTER to get the concatenation of cells to work...so I've got one method working.
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top