Invalid LOOKUP Function Results

[lmavroff]

The notes for LOOKUP function indicate the values in a row must be sorted or
invalid results will occur. Is there a way to get around this? How can I
use the LOOKUP function or any other lookup function to correctly return
results on an unsorted row of data? Below is an example of what I need to
accomplish:

C1 - C4 are column headers (text values)

A B C D
1 C1 C2 C3 C4
2 5 3 2 4

=lookup(5,A22,A11)

Actual / Invalid Result: C4
Correct / Needed Result: C1

 

[Mike H]

Try

=INDEX($A$1:$D$1,MATCH(A8,$A$2:$D$2,0))

Where A8 is the value you are matching

Mike

 

[Max]

Try instead: =INDEX(A11,MATCH(5,A22,0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik

 

[lmavroff]

Thanks, Mike.

Try

=INDEX($A$1:$D$1,MATCH(A8,$A$2:$D$2,0))

Where A8 is the value you are matching

Mike

 

[lmavroff]

Thanks, Max.

Try instead: =INDEX(A11,MATCH(5,A22,0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik

 

[lmavroff]

Is there a way to exclude values in hidden columns?

 

[lmavroff]

Is there a way to exclude values in hidden columns?

 

[Mike H]

I suspect you need vb for that. Try this UDF which must go in a general module

Call with
=visiblelookup(A22,A8,A11)

A22 is the range to look in
A8 is what to look for
A11 is the range to return
Both range must be the same length. Not extensively test but I think it
works with any 2 ranges of the same size rows or columns or mixed rows and
columns.

Function visiblelookup(rng As Range, lookvalue As String, returnrng As
Range) As String
If rng.Cells.Count <> returnrng.Cells.Count Then
visiblelookup = "Invalid Range"
Exit Function
End If
For Each C In rng
pos = pos + 1
If C.ColumnWidth <> 0 And CStr(C.Value) = lookvalue Then
visiblelookup = returnrng.Cells(pos)
Exit For
End If
Next
End Function


Mike

 

[Mike H]

it should now work for hidden columns or rows

Function visiblelookup(rng As Range, lookvalue As String, returnrng As
Range) As String
If rng.Cells.Count <> returnrng.Cells.Count Then
visiblelookup = "Invalid Range"
Exit Function
End If
For Each C In rng
pos = pos + 1
If C.ColumnWidth = 0 Or C.RowHeight = 0 Then GoTo itshidden
If CStr(C.Value) = lookvalue Then
visiblelookup = returnrng.Cells(pos)
Exit For
End If
itshidden:
Next
End Function

Mike