VLOOKUP with conditions

[Santaflare]

Hi,

-I'm tryingto create a spreadsheet which enables me to filter a list of salariesso that I can display the maximum wage for employees after an annual increase.I’ve tried using the VLOOKUP, but unfortunately I can’t get it to work asintended.

I’ve used the function in the example below. Column A contains currentsalary while column E shows which employees that has gotten an increase thisyear (columns B-D are used for salary history). What I’m trying to accomplishis to display the highest level of income for those employees that has got a raise.

In the attached example I want to display the data in cell A3: 2000, butit only generates a 0 unless all cells in column E are filled.

=VLOOKUP(MAX(A1:A8), A1:E8, 5, FALSE)

Any ideas for a workaround?


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]200[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]800[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]2000[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]300[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]2500[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]1500[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]1200[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]1800[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]3000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

This depends on your excel version but may work:

=MAXIFS(A1:A8,E1:E8,"<>")

 

[Santaflare]

-I'm using Excel 2016, and that function doesn’t seem to exist in this edition..
= /

 

[steve the fish]

Sure its fairly new. Heres another option which assumes you dont have max values that are negative:

=MAX(INDEX((E1:E8<>"")*A1:A8,0))

 

[Santaflare]

Sure its fairly new. Heres another option which assumes you dont have max values that are negative:

=MAX(INDEX((E1:E8<>"")*A1:A8,0))

Thanks,

Works like a charm!
= )

I tried to change the MAX to MIN in order to get the lowest number aswell, but that doesn’t seem to work. Any thoughts as to why?
(Unfortunately I need to extract both Highest, Lowest, Median and Averagefor the same range.)

 

[steve the fish]

Min is going to produce 0 if used like that. You will have to use array formula:

=MIN(IF(E1:E8<>"",A1:A8))

It has to be entered CTRL-SHIFT-ENTER or you will get 0 again.

 

[Santaflare]

Min is going to produce 0 if used like that. You will have to use array formula:

=MIN(IF(E1:E8<>"",A1:A8))

It has to be entered CTRL-SHIFT-ENTER or you will get 0 again.

Thanks a million, that really helped a lot!

 

[nanndu141]

Try these..

Max: =INDEX(A1:A8,MATCH(MAX(E1:E8),E1:E8,0),1)
Min: =INDEX(A1:A8,MATCH(MIN(E1:E8),E1:E8,0),1)

 

[steve the fish]

Try these..

Max: =INDEX(A1:A8,MATCH(MAX(E1:E8),E1:E8,0),1)
Min: =INDEX(A1:A8,MATCH(MIN(E1:E8),E1:E8,0),1)

They will produce the corresponding value in A for the minimum/maximum value of E which is not what is required.