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?

	A	B	C	D	E
1	1000				200
2	800				100
3	2000				300
4	2500
5	1500				50
6	1200
7	1800
8	3000

<tbody>
</tbody>

 

[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.