=vlookup on 2 columns

M

Mike

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
 
R

RagDyeR

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!
=====================================================

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
 
M

Mike

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
 
D

Dave Peterson

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)
 
R

Roger Govier

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.
 
Top