reverse lookup multiple values.

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
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]
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Book1
ABCDEF
1namedeptagesalary54787.57
2john1it4699178john1
3alex1hr3813887sam1
4david1sales4635365linda1
5mary1marketing7213553
6sam1it2799579
7linda1hr6977583
8john2sales6744368
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))),"")
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,154
Members
453,021
Latest member
Justyna P

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