Input in A14 department name, be able to get the data in the green, and the order and the original table data sequence. Use the LOOKUP only detected the department name at the top of a name and data, how to get here?

CodePudding user response:

This point, you should be more keyword query, however, the first merge cells, or normal, usually only the first cell have the content, the rest is blank, then in fact you this table may not be appropriate, not sure not the same department of the same name, so that general should have other key employees as a unique identifier, such as unit number or employee id number,

CodePudding user response:

Can't query merge, want to cancel the merge cells first, reoccupy after processing formula,

CodePudding user response:

Is not can, in some cases, can realize "query" merge cells (but formula may be more troublesome), particular case is particular analysis,
It remains to be seen your data source, and what's it like in the format of the desired results,

PS: merge cells create have two case,
One is normal, merging area only a value,
And one is more complex, multiple cells have multiple values

CodePudding user response:

Combining with the match function, using offset offset function
{=OFFSET ($B $5: $D $10, MATCH (B6 & amp; C6, $B $6: $$10 B & amp; $C $6: $C $10, 0), 2,1,1)}
Finally at the end of the curly brackets hold down Shift + Ctrl + Enter key can out