Question on Verschuuren Book

[eholz1]

Hello There,
I am stumped. I am using an old book, "Excel for Scientists and Engineers" (for Excel 2003).
My question refers to exercise number 6 on page 75 in this book. The question says
find a surge using an index function and two nested match functions, for any wind speed. I have played around with this, an failed! There is no ans in the book. So I am feeling pretty dumb.
Here is the table of data that needs to be searched: (b2:g4 is the range)
row 1 (wind MPH) 0 74 96 111 131 155
row 2 (severity) none weak moderate strong very strong devastating
row 3 (surge) 0ft 4ft 6ft 9ft 15ft 18ft

I am using cell E6 to enter a wind speed.

this works nice: =INDEX(B4:G4,MATCH(E6,B2:G2,1))
I do know that index can use a row and/or column, but I have not seen how to use a second match function, perhaps for a column?

Any hints on how to get this to work?

Thanks for the great podcasts, etc.

eric

 

[Derek Brown]

There is a third argument/parameter for the INDEX fuction, so something like the following is what you need (note that I have not tested this):

So, the following uses row 1 and the column number for which E6 matches B2:G2
=INDEX(B4:G4,1,MATCH(E6,B2:G2,1))
but note that if you want an exact match it should be:
=INDEX(B4:G4,1,MATCH(E6,B2:G2,0))

 

[eholz1]

Hello Derek,

I will give it a try. Thanks.

eric