Re: Combining Hlookup and vlookup
Max
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
Ragdyer
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" <demec...@yahoo.com> wrote in message
news:3CEC39A7-267E-4973...@microsoft.com...
ryguy7272
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
Laura.H...@gmail.com
> 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
> 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
Max
Thanks for posting the feedback<Laura.Halderman wrote >
> Max, I had the exact same problem with my data set as Dale. I used
yalic...@gmail.com
I can't believe that I didn't know it... Thank you Max!
Alice