CODEWITHSUNDEEP
excel-formula
Puja
Puja

Reputation: 11

Return multiple values by reading multiple columns and change based on the column selection

Screenshot of spreadsheet

Hello,

I am trying to get all the users with 'Yes' status from the data for 'A qualified' and in my results, the users list should change based on the selection. If selected 'B qualified' the Yes people under B qualified column should display.

Created a drop down for A qualified, B qualified and C qualified. I was able to get results for one column because I selected the data range. Having trouble with linking column headings and reading the results based on the headings. Please help

Upvotes: 1

Views: 215

Answers (1)

Peter K.
Peter K.

Reputation: 960

There are a few ways to accomplish this, I'll give you just the, IMHO, easiest and most understandable (to audit and for others that would look at your spreadsheet).

On a separate part of your workbook (can be visible next to your data on the same worksheet, or you can put this on a separate worksheet, and hide this if you want), you put a list (let's call it quali-matrix) of A qualified, B qualified, ..., and next to it, you put the cell range of the actual data, or the name of the range if you prefer working with named ranges. Then you choose a cell on that worksheet (let's say SheetX!A1) where you put the lookup results (lookup value is what's chosen in the drop down box and lookup range is quali-matrix).

So the above will give you a cell where the actual lookup range is that you will use in the big formula (the one that I gave you on SU) with the INDIRECT function. So the formula becomes:
{=IFERROR(INDEX($A$2:$A$9,SMALL(IF(INDIRECT(SheetX!$A$1)="Yes", ROW($A$2:$A$9)-MIN(ROW($A$2:$A$9))+1), ROWS($A$14:A14))),"")}

Upvotes: 1

Related Questions