Question on Exercise in "Excel for Sci and Eng - 2003"

[eholz1]

Hello Again,

I have been working with the book, "Excel for Scientists and Engineers" by Dr. Gerard
Verschuuren. this is the Excel 2003 vintage. On page 75 of this book are some good
exercises using various flavors of the lookup functions. I have a question about Exercise 34, item 5. It says, "...find severity of any MPH wind setting by using one of the LOOKUP functions with a nested MATCH function." This is real easy with INDEX and MATCH, and VLOOKUP and MATCH. I thought I would try it with HLOOKUP as well, but I am thinking this may not be possible in this case. For this example assume that cell E6 is where I enter the windspeed, Range is the cells with data for the "Hurricanes", without the labels. I know what I want, =HLOOKUP(E6,Range,row,true), this works fine. In order to return a value for the severity, I know that I need to get data from "row 2" in the Range. If I were to use MATCH, it would need to return the correct row (always "2") for the severity value. I have not yet been able to see how to get MATCH to return the correct "row" (really a column?) in all cases.

I can get it to "work" using this syntax:
=HLOOKUP(E6,B2:G4,MATCH("none",B3:G3,1) -1, TRUE).

Here the severity is in the range (for the match func) of b3:g3. I put a text value as my search. If I pick "weak", then I need to subtract "2" from the result that MATCH returns, and so on. I figured that out just by playing around with things, but I am not sure that that is a valid approach.

Any ideas on this?

Thanks for plenty of challenge.

eric / eholz1

 

[MrExcel]

I would suggest this: =HLOOKUP($J$1,$B$1:$G$3,MATCH(I2,$A$1:$A$3,0),1)
assuming that the table is in A1:G3 (minus the 1st column) and the search cell in J1 (followed by J2 and J2 for results).

Bill

 

[eholz1]

Hello Bill,

Thanks for the reply. I love your podcasts. Particularly the one on the pull function.

I did play around with the response, in my case the cells with the data were in the
range of a1:f3, i can place the "windspeed" (or the search value) in a cell, and use MATCH
such that it looking for an approximate match with the cells you site in your statement.
Translated into english, it is: "try to match anything in row two of my range". HLOOKUP is the easy part, searching the range for the "speed", and using MATCH for the row value.

In one sense this defies logic (at least my logic!) - but it works.

Thanks,

eholz1 (aka: eric)