问题:

I have a table in Excel which contains 2 columns of text, and two columns of numbers. The numbers refer to the position in which a search string is found within a body of text - and if they aren't found then the cell is left empty (eg ISERROR(myfunctions(),""). A screenshot is here (I dont have enough rep to embed a screenshot...)

I wish to return the value in the first column where the minimum and maximum values are from both of the number columns. This to me is simple:

=VLOOKUP(MIN(E3:E7),C3:E7,1)

=VLOOKUP(MAX(E3:E7),C3:E7,1)

This evaluates to #N/A no matter what I try. What is the reason for this?

回答1:

VLOOKUP always does the lookup on the first column in the lookup table so you are looking up the Min value in the weather column and it does not match.

You can either restructure your data so that the location column is first or use INDEX MATCH

=INDEX(C3:E7,MATCH(MIN(E3:E7),E3:E7,0),1)

回答2:

cut and paste column e to column c. afterwards it should be column c = location, d = weather, e = icon. note, vlookup will display the first value found, top down. So since you have 3 "100000" it is returning C3 first as the MAX value.

min weather formula: =IFERROR(VLOOKUP(MIN($C$3:$C$7),$C$3:$E$7,2,FALSE),"")

max weather formula: =IFERROR(VLOOKUP(MAX($C$3:$C$7),$C$3:$E$7,2,FALSE),"")