How do I retrive data from my spreadsheet when I type in seperate cells a row
heading(A-column) and a column heading(row8)? I'm trying to retrieve the
data to be used in another calculation. When I type the text in a blank
cell, I cannot get it to recognize that it matches the headers. I thought
that if I could pick out the headers, I could have it send back a value,
which I could then use that in the INDEX function. I've tried HLOOKUP and
VLOOKUP and I obviously don't understand what's asking me for because I'm not
sure how it came up with the answer.
I'm using Excel 2000.
--
sdaniels

Debra Dalgleish has lots of notes on =vlookup() and =index(match()) here:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

I bet one of those will ease your pain!

sdaniels wrote:
>
> How do I retrive data from my spreadsheet when I type in seperate cells a row
> heading(A-column) and a column heading(row8)? I'm trying to retrieve the
> data to be used in another calculation. When I type the text in a blank
> cell, I cannot get it to recognize that it matches the headers. I thought
> that if I could pick out the headers, I could have it send back a value,
> which I could then use that in the INDEX function. I've tried HLOOKUP and
> VLOOKUP and I obviously don't understand what's asking me for because I'm not
> sure how it came up with the answer.
> I'm using Excel 2000.
> --
> sdaniels

--

Dave Peterson

Hi

I think this is what you are after! If not let me know.... Just copy the
code below into a standard module.

*************************************************
Code starts
*************************************************

Function DataLookUp(strDataRange As String, strRowHeader, strColHeader, _
Optional varNone As Variant = 0) As Variant
'returns data element in a 2 dimensional defined range, with row and column
headers
'Inputs:
'strDataRange - defined range in active workbook from which to look up
data
'strRowHeader - what row do we want
'strColHeader - what column do we want
'varNone - optional argument - value to return if one or more of
' strRowHeader, strColHeader, range do not exist (defaults
to 0)
'Outputs
' Data element extracted from defined range

Dim rng As Range
Dim intRow As Integer
Dim intCol As Integer
On Error GoTo ProcError
Set rng = Range(ActiveWorkbook.Names(strDataRange).RefersTo)
DataLookUp = Intersect(rng.Rows(1).Find(strColHeader).EntireColumn, _
rng.Columns(1).Find(strRowHeader).EntireRow).Value

ProcExit:
Set rng = Nothing
Exit Function


ProcError:
DataLookUp = varNone
Resume ProcExit

End Function

************************************
Code Ends
************************************

"sdaniels" wrote:

> How do I retrive data from my spreadsheet when I type in seperate cells a row
> heading(A-column) and a column heading(row8)? I'm trying to retrieve the
> data to be used in another calculation. When I type the text in a blank
> cell, I cannot get it to recognize that it matches the headers. I thought
> that if I could pick out the headers, I could have it send back a value,
> which I could then use that in the INDEX function. I've tried HLOOKUP and
> VLOOKUP and I obviously don't understand what's asking me for because I'm not
> sure how it came up with the answer.
> I'm using Excel 2000.
> --
> sdaniels

As I understand it, you are trying to do a double-lookup.
With

Set up your rows and columns, and then in cell G1, enter the value you want
to look for in the rows, and in H1, enter the value you want to look for in
your columns. Then, use the function below:
=INDEX(B2:E5,MATCH(G1,A2:A5),MATCH(H1,B1:E1))


This is just an example; change to suit your specific needs.
Hope that helps,
Ryan--

--
RyGuy


"sdaniels" wrote:

> How do I retrive data from my spreadsheet when I type in seperate cells a row
> heading(A-column) and a column heading(row8)? I'm trying to retrieve the
> data to be used in another calculation. When I type the text in a blank
> cell, I cannot get it to recognize that it matches the headers. I thought
> that if I could pick out the headers, I could have it send back a value,
> which I could then use that in the INDEX function. I've tried HLOOKUP and
> VLOOKUP and I obviously don't understand what's asking me for because I'm not
> sure how it came up with the answer.
> I'm using Excel 2000.
> --
> sdaniels

Thanks for your help!
--
sdaniels


"sdaniels" wrote:

> How do I retrive data from my spreadsheet when I type in seperate cells a row
> heading(A-column) and a column heading(row8)? I'm trying to retrieve the
> data to be used in another calculation. When I type the text in a blank
> cell, I cannot get it to recognize that it matches the headers. I thought
> that if I could pick out the headers, I could have it send back a value,
> which I could then use that in the INDEX function. I've tried HLOOKUP and
> VLOOKUP and I obviously don't understand what's asking me for because I'm not
> sure how it came up with the answer.
> I'm using Excel 2000.
> --
> sdaniels

I tried your suggestion and it gave me funny data returned. This also
happened when I used =vlookup(). Could there be something wrong with my data?
--
sdaniels


"ryguy7272" wrote:

> As I understand it, you are trying to do a double-lookup.
> With
>
> Set up your rows and columns, and then in cell G1, enter the value you want
> to look for in the rows, and in H1, enter the value you want to look for in
> your columns. Then, use the function below:
> =INDEX(B2:E5,MATCH(G1,A2:A5),MATCH(H1,B1:E1))
>
>
> This is just an example; change to suit your specific needs.
> Hope that helps,
> Ryan--
>
> --
> RyGuy
>
>
> "sdaniels" wrote:
>
> > How do I retrive data from my spreadsheet when I type in seperate cells a row
> > heading(A-column) and a column heading(row8)? I'm trying to retrieve the
> > data to be used in another calculation. When I type the text in a blank
> > cell, I cannot get it to recognize that it matches the headers. I thought
> > that if I could pick out the headers, I could have it send back a value,
> > which I could then use that in the INDEX function. I've tried HLOOKUP and
> > VLOOKUP and I obviously don't understand what's asking me for because I'm not
> > sure how it came up with the answer.
> > I'm using Excel 2000.
> > --
> > sdaniels