Complicated Lookup Problem

[dgp]

I have a complicated lookup problem that I'm struggling to solve. I was
given a speadsheet containing data in the following format:

A B C D E
1 800 1600 2000 3000
2 800 0.05 0.02 11 0.56
3 850 0.02 0.006 6.2 0.65
4 900 0.005 0.013 0.5 0.23
5 950 0.027 20.2 0.25 5.65
6 1000 10.05 0.1 0.3 0.25

B1:E1 contains Frequency values
Column A contains RPM values
Range B2:E6 contains Amplitudes corresponding to the given Frequency
and RPM Headers

I need to lookup up the RPM value for in Column A for a specified
Frequency and Amplitude within the Frequency column.


For example, I would like to find the value in Column A corresponding
to a Frequency (Row 1) of 2000 and an amplitude in the 2000 column of
11 - the result would be 800. Similarly, 16 & 20.2 would give 950.

Any help would be greatly appreciated.

Dave Parker

 

[Max]

One way ..

Reference table in A1:E6, as posted

Assuming the lookup pairs of values for frequency & amplitude
are listed in H1 & I1 down, eg:

2000 11
1600 20.2
etc

Then we could put in J1:
=INDEX(A:A,MATCH(I1,OFFSET(A:A,,MATCH(H1,$B$1:$E$1,0)),0))
and copy J1 down

Col J will return the required RPM values from col A, viz. we'd get:

2000 11 800
1600 20.2 950

 

[dgp]

Thank you Max. It worked perfectly for my problem.

 

[Max]

Glad to hear that, Dave !
Thanks for the feedback ..