Invalid LOOKUP Function Results
 

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,A2:D2,A1:D1)

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

Invalid LOOKUP Function Results
 

Try

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

Where A8 is the value you are matching

Mike

"lmavroff" wrote:

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,A2:D2,A1:D1)

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

Invalid LOOKUP Function Results
 

Try instead: =INDEX(A1:D1,MATCH(5,A2:D2,0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"lmavroff" wrote:
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,A2:D2,A1:D1)

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

Invalid LOOKUP Function Results
 

Thanks, Mike.

"Mike H" wrote:

Try

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

Where A8 is the value you are matching

Mike

"lmavroff" wrote:

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,A2:D2,A1:D1)

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

Invalid LOOKUP Function Results
 

Thanks, Max.

"Max" wrote:

Try instead: =INDEX(A1:D1,MATCH(5,A2:D2,0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"lmavroff" wrote:
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,A2:D2,A1:D1)

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

Invalid LOOKUP Function Results
 

Is there a way to exclude values in hidden columns?

"Mike H" wrote:

Try

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

Where A8 is the value you are matching

Mike

"lmavroff" wrote:

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,A2:D2,A1:D1)

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

Invalid LOOKUP Function Results
 

Is there a way to exclude values in hidden columns?

"Max" wrote:

Try instead: =INDEX(A1:D1,MATCH(5,A2:D2,0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"lmavroff" wrote:
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,A2:D2,A1:D1)

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

Invalid LOOKUP Function Results
 

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

Call with
=visiblelookup(A2:D2,A8,A1:D1)

A2:D2 is the range to look in
A8 is what to look for
A1:D1 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

"lmavroff" wrote:

Is there a way to exclude values in hidden columns?

"Mike H" wrote:

Try

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

Where A8 is the value you are matching

Mike

"lmavroff" wrote:

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,A2:D2,A1:D1)

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

Invalid LOOKUP Function Results
 

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

"Mike H" wrote:

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

Call with
=visiblelookup(A2:D2,A8,A1:D1)

A2:D2 is the range to look in
A8 is what to look for
A1:D1 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

"lmavroff" wrote:

Is there a way to exclude values in hidden columns?

"Mike H" wrote:

Try

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

Where A8 is the value you are matching

Mike

"lmavroff" wrote:

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,A2:D2,A1:D1)

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