=vlookup on 2 columns

Hello All,
Using Excel XP.

I have two columns of data, for example, say temps in column A and wind
speeds in column B.
I want a vlookup formula that would give me a windchill in column C based on
a match in column A & B.

A B C
------------------------------
1 47 6 45
2 47 7 45
3 47 8 44
4 47 9 44
5 47 10 43
6 47 11 43
7 47 12 42
8 47 13 42
9 47 14 41

I checked the old messages in the newsgroup and found this response from Peo
Sjoblom.
I tried applying the formula in the above example by changing the Ranges to
the cells of A to C
and 1 to 9. I'm not sure what goes where the 1 is after MATCH(1,......I
tried putting down the 47 from colunm and leaving it as the 1, but get an
#N/A.
Would appreciate if anyone could help me out on this one,

Thanks,
Mike

=INDEX(Range3,MATCH(1,(Range1="team")*(Range2="jobtitle"),0))

entered with ctrl + shift & enter

where Range3 is the range where you want the result returned from
Regards,

Peo Sjoblom

Place temperature to lookup in D1,
Place speed in D2,

And try this *array* formula:

=INDEX(C1:C9,MATCH(1,(A1:A9=D1)*(B1:B9=D2),0))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Now, if your actual datalist might have gaps in speed or temperature
entries, you could try revising the *kind* of match required to return your
wind chill factor.

=INDEX(C1:C9,MATCH(1,(A1:A9>=D1)*(B1:B9>=D2),0))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"Mike" <windme@cox.net> wrote in message
news:DhpHf.103411$0G.5901@dukeread10...
Hello All,
Using Excel XP.

I have two columns of data, for example, say temps in column A and wind
speeds in column B.
I want a vlookup formula that would give me a windchill in column C based on
a match in column A & B.

A B C
------------------------------
1 47 6 45
2 47 7 45
3 47 8 44
4 47 9 44
5 47 10 43
6 47 11 43
7 47 12 42
8 47 13 42
9 47 14 41

I checked the old messages in the newsgroup and found this response from Peo
Sjoblom.
I tried applying the formula in the above example by changing the Ranges to
the cells of A to C
and 1 to 9. I'm not sure what goes where the 1 is after MATCH(1,......I
tried putting down the 47 from colunm and leaving it as the 1, but get an
#N/A.
Would appreciate if anyone could help me out on this one,

Thanks,
Mike

=INDEX(Range3,MATCH(1,(Range1="team")*(Range2="jobtitle"),0))

entered with ctrl + shift & enter

where Range3 is the range where you want the result returned from
Regards,

Peo Sjoblom

Hi RD,
that formula works, however i will be putting in LOTS of wind chills. Is
there a way to fill in the formula that will change the temp to 46, 45, 44,
........ etc and the wind speed from 7, 8, 9....
and on.?
It would be too cumbersome to manually go into every formula and change the
temp and wind speed.
Thanks for you help,

Mike

"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
news:%236sejSzLGHA.1472@TK2MSFTNGP09.phx.gbl...
> Place temperature to lookup in D1,
> Place speed in D2,
>
> And try this *array* formula:
>
> =INDEX(C1:C9,MATCH(1,(A1:A9=D1)*(B1:B9=D2),0))
>
> --
> Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of
> the
> regular <Enter>, which will *automatically* enclose the formula in curly
> brackets, which *cannot* be done manually.
>
> Now, if your actual datalist might have gaps in speed or temperature
> entries, you could try revising the *kind* of match required to return
> your
> wind chill factor.
>
> =INDEX(C1:C9,MATCH(1,(A1:A9>=D1)*(B1:B9>=D2),0))
>
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
> "Mike" <windme@cox.net> wrote in message
> news:DhpHf.103411$0G.5901@dukeread10...
> Hello All,
> Using Excel XP.
>
> I have two columns of data, for example, say temps in column A and wind
> speeds in column B.
> I want a vlookup formula that would give me a windchill in column C based
> on
> a match in column A & B.
>
> A B C
> ------------------------------
> 1 47 6 45
> 2 47 7 45
> 3 47 8 44
> 4 47 9 44
> 5 47 10 43
> 6 47 11 43
> 7 47 12 42
> 8 47 13 42
> 9 47 14 41
>
> I checked the old messages in the newsgroup and found this response from
> Peo
> Sjoblom.
> I tried applying the formula in the above example by changing the Ranges
> to
> the cells of A to C
> and 1 to 9. I'm not sure what goes where the 1 is after MATCH(1,......I
> tried putting down the 47 from colunm and leaving it as the 1, but get an
> #N/A.
> Would appreciate if anyone could help me out on this one,
>
> Thanks,
> Mike
>
> =INDEX(Range3,MATCH(1,(Range1="team")*(Range2="jobtitle"),0))
>
> entered with ctrl + shift & enter
>
> where Range3 is the range where you want the result returned from
> Regards,
>
> Peo Sjoblom
>
>
>
>

Maybe you could just use a formula:

With the temperature in A1 (in degrees F)
and the windspeed in A2 (in MPH)
put this in A3:
= 35.74 + 0.6215*A1 - 35.75*(A2^0.16) + 0.4275*A1 *(A2^0.16)

Taken from:
http://www.nws.noaa.gov/om/windchill/
windchill (F) = 35.74 + 0.6215T - 35.75*(V^0.16) + 0.4275T *(V^0.16)
T = Temp in degrees Fahrenheit
V = Windspeed (MPH)


Mike wrote:
>
> Hello All,
> Using Excel XP.
>
> I have two columns of data, for example, say temps in column A and wind
> speeds in column B.
> I want a vlookup formula that would give me a windchill in column C based on
> a match in column A & B.
>
> A B C
> ------------------------------
> 1 47 6 45
> 2 47 7 45
> 3 47 8 44
> 4 47 9 44
> 5 47 10 43
> 6 47 11 43
> 7 47 12 42
> 8 47 13 42
> 9 47 14 41
>
> I checked the old messages in the newsgroup and found this response from Peo
> Sjoblom.
> I tried applying the formula in the above example by changing the Ranges to
> the cells of A to C
> and 1 to 9. I'm not sure what goes where the 1 is after MATCH(1,......I
> tried putting down the 47 from colunm and leaving it as the 1, but get an
> #N/A.
> Would appreciate if anyone could help me out on this one,
>
> Thanks,
> Mike
>
> =INDEX(Range3,MATCH(1,(Range1="team")*(Range2="jobtitle"),0))
>
> entered with ctrl + shift & enter
>
> where Range3 is the range where you want the result returned from
> Regards,
>
> Peo Sjoblom

--

Dave Peterson

Hi Mike

It looks like Dave has given you a formula which will do away with your
lookups, but id you still did need to use the lookup method, a way
around your problem would be to modify Ragdyer's formula to
=INDEX(C1:C9,MATCH(1,(A1:A9>=47-ROW(1:1))*(B1:B9>=Row(7:7),0))
Copy down and the temperature will fall for each successive row, and
wind speed would rise.

--
Regards

Roger Govier


"Mike" <windme@cox.net> wrote in message
news:t7qHf.103424$0G.48383@dukeread10...
> Hi RD,
> that formula works, however i will be putting in LOTS of wind chills.
> Is there a way to fill in the formula that will change the temp to 46,
> 45, 44, ....... etc and the wind speed from 7, 8, 9....
> and on.?
> It would be too cumbersome to manually go into every formula and
> change the temp and wind speed.
> Thanks for you help,
>
> Mike
>
> "RagDyeR" <ragdyer@cutoutmsn.com> wrote in message
> news:%236sejSzLGHA.1472@TK2MSFTNGP09.phx.gbl...
>> Place temperature to lookup in D1,
>> Place speed in D2,
>>
>> And try this *array* formula:
>>
>> =INDEX(C1:C9,MATCH(1,(A1:A9=D1)*(B1:B9=D2),0))
>>
>> --
>> Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead
>> of the
>> regular <Enter>, which will *automatically* enclose the formula in
>> curly
>> brackets, which *cannot* be done manually.
>>
>> Now, if your actual datalist might have gaps in speed or temperature
>> entries, you could try revising the *kind* of match required to
>> return your
>> wind chill factor.
>>
>> =INDEX(C1:C9,MATCH(1,(A1:A9>=D1)*(B1:B9>=D2),0))
>>
>> --
>>
>> HTH,
>>
>> RD
>> =====================================================
>> Please keep all correspondence within the Group, so all may benefit!
>> =====================================================
>>
>> "Mike" <windme@cox.net> wrote in message
>> news:DhpHf.103411$0G.5901@dukeread10...
>> Hello All,
>> Using Excel XP.
>>
>> I have two columns of data, for example, say temps in column A and
>> wind
>> speeds in column B.
>> I want a vlookup formula that would give me a windchill in column C
>> based on
>> a match in column A & B.
>>
>> A B C
>> ------------------------------
>> 1 47 6 45
>> 2 47 7 45
>> 3 47 8 44
>> 4 47 9 44
>> 5 47 10 43
>> 6 47 11 43
>> 7 47 12 42
>> 8 47 13 42
>> 9 47 14 41
>>
>> I checked the old messages in the newsgroup and found this response
>> from Peo
>> Sjoblom.
>> I tried applying the formula in the above example by changing the
>> Ranges to
>> the cells of A to C
>> and 1 to 9. I'm not sure what goes where the 1 is after
>> MATCH(1,......I
>> tried putting down the 47 from colunm and leaving it as the 1, but
>> get an
>> #N/A.
>> Would appreciate if anyone could help me out on this one,
>>
>> Thanks,
>> Mike
>>
>> =INDEX(Range3,MATCH(1,(Range1="team")*(Range2="jobtitle"),0))
>>
>> entered with ctrl + shift & enter
>>
>> where Range3 is the range where you want the result returned from
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>>
>>
>
>