Need help with LOOKUP function in Excel!

[Akaz1976]

Hi,

Here is what i want to do:

For eg, I have data in column A which is month end dates from 31-jan-01 to 31-dec-01. I have reference cell where i can enter any of the 12 dates in the data range. Say i put 30-jun-01 in the reference cell. I want a fuction that will 'lookup' 30-jun-01 in column A and return the date below cell containing 30-jun-01.

The reason i want to do this is that i have a data set containing TSE300 month end index levels going back to base year of 1982. I want my spread sheet to able to calculate a new TSE index using any base year that i give. ie if i put in dec 31, 96, i want it to use that as base and calculate the index from there going forward.

thanx

Akaz

 

[Akaz1976]

OR put another way, i need a lookup funtion that instead of:

Looking for reference value in a column and then returning a value from row containing reference value. (eg. VLOOKUP)

OR

Looking for reference value in a row and then returning a value from column containing reference value. (eg. HLOOKUP)

actually looks up value in a column and returns the value from the same column

Thanx

Akaz

 

[theknight571]

Here's a lookup function I just put together. It worked in the simple example I setup. YMMV.

It should go in the Module1 of your workbook. ( Tools...Macro...VB Editor... )

LMK if it helps or if you need any more information.



<<
Function MyLookup(InValue As Variant, TheRange As Range) As Variant
FoundIt = False
For Each MyCell In TheRange
If FoundIt = True Then Exit For
If MyCell.Value = InValue Then
FoundIt = True
End If
Next
MyLookup = MyCell.Value
End Function
>>



- TK

P.S. The browser seems to remove the spacing making the code hard to read, but it's there.

P.P.S. Just thought I would include what the call looks like from a cell...

Syntax: =MyLookup(ValueToLookup,RangeToLookIn)
Ex: =MyLookup(D8,$C$8:$C$18)

 

[Akaz1976]

YOU MY FRIEND ARE A GENIUS and a life saver

thank u very very much

Akaz

 

[theknight571]

You are most welcome.

- TK

 

[RayH]

This formula will also work:

=OFFSET(A6,MATCH(A1,A6:A12,0)+1,0)

A6 = top cell of column of lookup values
A1 = lookup value wanted
A6:A12 = range of lookup values (change as needed)

 

[theknight571]

Cool...I'll have to note that one myself.

- TK