reverse lookup multiple values.

[lezawang]

Hi

I will find the average salary using =average(D2:D8). What I also want to know the names of people who make more than average and the names who make more than average. How can I do that? Thank you

=average(D2:D8)[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]54787.57[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]name[/TD]
[TD="width: 64"]dept[/TD]
[TD="width: 64"]age[/TD]
[TD="width: 64"]salary[/TD]
[/TR]
[TR]
[TD]john1[/TD]
[TD]it[/TD]
[TD]46[/TD]
[TD]99178[/TD]
[/TR]
[TR]
[TD]alex1[/TD]
[TD]hr[/TD]
[TD]38[/TD]
[TD]13887[/TD]
[/TR]
[TR]
[TD]david1[/TD]
[TD]sales[/TD]
[TD]46[/TD]
[TD]35365[/TD]
[/TR]
[TR]
[TD]mary1[/TD]
[TD]marketing[/TD]
[TD]72[/TD]
[TD]13553[/TD]
[/TR]
[TR]
[TD]sam1[/TD]
[TD]it[/TD]
[TD]27[/TD]
[TD]99579[/TD]
[/TR]
[TR]
[TD]linda1[/TD]
[TD]hr[/TD]
[TD]69[/TD]
[TD]77583[/TD]
[/TR]
[TR]
[TD]john2[/TD]
[TD]sales[/TD]
[TD]67[/TD]
[TD]44368[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F
1	name	dept	age	salary		54787.57
2	john1	it	46	99178		john1
3	alex1	hr	38	13887		sam1
4	david1	sales	46	35365		linda1
5	mary1	marketing	72	13553
6	sam1	it	27	99579
7	linda1	hr	69	77583
8	john2	sales	67	44368
Sheet1

In F1 just enter:

=AVERAGE(D2:D8)

In F2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$8,SMALL(IF($D$2:$D$8>$F$1,ROW($A$2:$A$8)-ROW($A$2)+1),ROWS($1:1))),"")

 

[lezawang]

Thanks for your help and reply. I appreciate it. May I ask you to break it down so I can understand it. I am trying to understand it but it is a bit hard for me. Thank you.

 

[Aladin Akyurek]

Thanks for your help and reply. I appreciate it. May I ask you to break it down so I can understand it. I am trying to understand it but it is a bit hard for me. Thank you.

1. whenever a cell of D2:D8 is greater (>) than the average value if F1, deliver the adjusted row number for that cell

2. there can be one or more row number results --> {1;FALSE;FALSE;FALSE;5;6;FALSE}

3. when results of [2] is fed to SMALL, SMALL returns one by one the row numbers, that is, first 1, then 5, and then 6, to the outer INDEX.

4. INDEX in turn delivers the values from A2:A8 at each of the rows it is fed with.


Hope this helps.