VLOOKUP looking in second column of table_array instead of first?

[slam]

Is this possible?

I can't move my columns around, so need some way to look up the 2nd column of the table array for values that match instead of the 1st.

I've tried THREED from morefunc, but am not sure if this is really what I need. Can't get this to work anyway:

=VLOOKUP(1,THREED(Australia!$B$2:$D$17),3,FALSE)

 

[barry houdini]

Hello slam,

Can't you just change the table so that the column you need is the first in the range?

Can you define exactly what you want to do - you want to lookup 1 in which column? and which column should the result come from

 

[slam]

Hello slam,

Can't you just change the table so that the column you need is the first in the range?

Can you define exactly what you want to do - you want to lookup 1 in which column? and which column should the result come from

I'm trying to find a 1 in column D (D2 to D17), and I'm trying to return the corresponding value in column B (B2 to B17).

I really can't change the columns unfortunately. I have so many other formulas dependent on this data, and more importantly, it just doesn't make sense to have it ordered that way.

 

[barry houdini]

You can use INDEX and MATCH in combination for "left lookups" - try this formula

=INDEX(Australia!$B$2:$B$17,MATCH(1,Australia!$D$2:$D$17,0))

 

[slam]

You can use INDEX and MATCH in combination for "left lookups" - try this formula

=INDEX(Australia!$B$2:$B$17,MATCH(1,Australia!$D$2:$D$17,0))

Thanks, that worked great. How would I change that formula to find the highest value in the same column, and have it display nothing when the cells in column D are not populated. I assume I have to use an IF and MAX, but not sure how to piece it together....