Combining Hlookup and vlookup
 

I am trying to use the vlookup and hlookup to "cross hair" a cell. I have
used this combination before but I cannot remember the formula. I have items
in the first column and a month identifier in the following columns. So I am
trying to look up Beans in column A with the production in columns that have
months listed in them, i.e. column B is January, column c is February, etc.

Thanks

Dale

Combining Hlookup and vlookup
 

One way - use index/match

Assume source table below is in A1:C4
January February
Hair 45 84
Beans 42 61
Prod3 72 29
(month col headers are text)

Assume you have the inputs in E2:F2 down
Beans January
Prod3 February

You could put in G2:
=INDEX($B$2:$C$4,MATCH(E2,$A$2:$A$4,0),MATCH(F2,$B $1:$C$1,0))
and copy down to return the cross-hair results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dale" wrote:
I am trying to use the vlookup and hlookup to "cross hair" a cell. I have
used this combination before but I cannot remember the formula. I have items
in the first column and a month identifier in the following columns. So I am
trying to look up Beans in column A with the production in columns that have
months listed in them, i.e. column B is January, column c is February, etc.

Thanks

Dale

Combining Hlookup and vlookup
 

Another way ... use XL's "intersection operator", which is a <space.

Say January and February are in B1 and C1 respectively.
Say Hair, Beans, and Prod are in A2 to A4 respectively.

=Hair February
returns 84

And
=January Prod
returns 72

To make this work, you'll need:
<Tools <Options <Calculation tab
And "Accept Labels In Formulas" *to be checked*.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Max" wrote in message
...
One way - use index/match

Assume source table below is in A1:C4
January February
Hair 45 84
Beans 42 61
Prod3 72 29
(month col headers are text)

Assume you have the inputs in E2:F2 down
Beans January
Prod3 February

You could put in G2:
=INDEX($B$2:$C$4,MATCH(E2,$A$2:$A$4,0),MATCH(F2,$B $1:$C$1,0))
and copy down to return the cross-hair results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dale" wrote:
I am trying to use the vlookup and hlookup to "cross hair" a cell. I
have
used this combination before but I cannot remember the formula. I have
items
in the first column and a month identifier in the following columns. So
I am
trying to look up Beans in column A with the production in columns that
have
months listed in them, i.e. column B is January, column c is February,
etc.

Thanks

Dale

Combining Hlookup and vlookup
 

When you get your Index/Match function working, you may want to employ this
technique too (found the code on this DG a short time ago):

'----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
With .EntireRow
..FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = 5
End With
With .Borders(xlBottom)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = 5
End With
..Interior.ColorIndex = 20
End With
End With
With .EntireColumn
..FormatConditions.Delete
..FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlLeft)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = 5
End With
With .Borders(xlRight)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = 5
End With
..Interior.ColorIndex = 20
End With
End With

..FormatConditions.Delete
..FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
..FormatConditions(1).Interior.ColorIndex = 36
End With

End Sub


Regards,
Ryan--


--
RyGuy


"Ragdyer" wrote:

Another way ... use XL's "intersection operator", which is a <space.

Say January and February are in B1 and C1 respectively.
Say Hair, Beans, and Prod are in A2 to A4 respectively.

=Hair February
returns 84

And
=January Prod
returns 72

To make this work, you'll need:
<Tools <Options <Calculation tab
And "Accept Labels In Formulas" *to be checked*.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Max" wrote in message
...
One way - use index/match

Assume source table below is in A1:C4
January February
Hair 45 84
Beans 42 61
Prod3 72 29
(month col headers are text)

Assume you have the inputs in E2:F2 down
Beans January
Prod3 February

You could put in G2:
=INDEX($B$2:$C$4,MATCH(E2,$A$2:$A$4,0),MATCH(F2,$B $1:$C$1,0))
and copy down to return the cross-hair results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dale" wrote:
I am trying to use the vlookup and hlookup to "cross hair" a cell. I
have
used this combination before but I cannot remember the formula. I have
items
in the first column and a month identifier in the following columns. So
I am
trying to look up Beans in column A with the production in columns that
have
months listed in them, i.e. column B is January, column c is February,
etc.

Thanks

Dale

Combining Hlookup and vlookup
 

On Jan 26, 7:20 pm, Max wrote:
One way - use index/match

Assume source table below is in A1:C4
January February
Hair 45 84
Beans 42 61
Prod3 72 29
(month col headers are text)

Assume you have the inputs in E2:F2 down
Beans January
Prod3 February

You could put in G2:
=INDEX($B$2:$C$4,MATCH(E2,$A$2:$A$4,0),MATCH(F2,$B $1:$C$1,0))
and copy down to return the cross-hair results.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---

"Dale" wrote:
I am trying to use the vlookup and hlookup to "cross hair" a cell. I have
used this combination before but I cannot remember the formula. I have items
in the first column and a month identifier in the following columns. So I am
trying to look up Beans in column A with the production in columns that have
months listed in them, i.e. column B is January, column c is February, etc.

Thanks

Dale

Max,I had the exact same problem with my data set as Dale. I used
your recommendation, and problem solved!!! This post saved me tons of
time and frustration. Thank you so much for the clear and detailed
explanation!!!!

Laura

Combining Hlookup and vlookup
 

Glad to hear that, Laura
Thanks for posting the feedback
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
<Laura.Halderman wrote
Max, I had the exact same problem with my data set as Dale. I used
your recommendation, and problem solved!!! This post saved me tons of
time and frustration. Thank you so much for the clear and detailed
explanation!!!!

Laura

Combining Hlookup and vlookup
 

O.M.G.

I can't believe that I didn't know it... Thank you Max!


Alice

On Saturday, January 26, 2008 3:20:00 PM UTC-8, Max wrote:
One way - use index/match

Assume source table below is in A1:C4
January February
Hair 45 84
Beans 42 61
Prod3 72 29
(month col headers are text)

Assume you have the inputs in E2:F2 down
Beans January
Prod3 February

You could put in G2:
=INDEX($B$2:$C$4,MATCH(E2,$A$2:$A$4,0),MATCH(F2,$B $1:$C$1,0))
and copy down to return the cross-hair results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dale" wrote:
I am trying to use the vlookup and hlookup to "cross hair" a cell. I have
used this combination before but I cannot remember the formula. I have items
in the first column and a month identifier in the following columns. So I am
trying to look up Beans in column A with the production in columns that have
months listed in them, i.e. column B is January, column c is February, etc.

Thanks

Dale