Matching / lookup problem

[Nick]

I have a nice problem to submit today...

I need to match H column value with lookup value and
lookup vector established as follows in cell E8
=LOOKUP(A8&C8&D8,A1:A1000&G1:G1000&D11000,H1:H1000)

The LU value (A8&C8&D8) returns C100P27. However, with
the condition (A1:A10&G1:G10&D110) in the LU vector,
there is 2 values that match this condition on Line 3
and Line 6, and have the respective results of (414.0)
(422.6).

Here are the results for different conditions:
=LOOKUP(A8&C8&D8,A1:A1000&G1:G1000&D11000,H1:H1000)=0
=LOOKUP(A8&C8&D8,A1:A10&G1:G10&D110,H1:H10)= 414
=LOOKUP(A8&C8&D8,A2:A10&G2:G10&D210,H2:H10)= 422

I need the formula to go Descending (Line 10 towards Line
1) and get the 422 (ascending I would have used INDEX and
MATCH formula), and, in addition to this, I need to get
to go down to Line 1000 (no data from Line 11 to Line
1000), giving airspace for my database.

How removing Line 1 in the lookup would affect the
results...

Using the Lookup function

A B C D E F G H

1 B 40 400 S34 416 419 Sto 419
2 B 40 400 P24 421 423 Sto 424
3 C 10 300 P27 411 413 100 414
4 C 20 100 P27 414 417 400 418
5 C 30 400 S21 414 417 100 418
6 C 30 400 P27 418 421 100 422
7 C 40 100 S21 418 420 200 421
8 C 40 100 P27 414 2.3 200 3.3
9 C 50 200 S21 421 423 Sto 423
10 C 50 200 P27 3.3 5.8 Sto 6.3

 

[Leo Heuser]

Nick

It looks to me, that you need a formula like this array formula:

=INDEX(H1:H10,MAX((A8&C8&D8=A1:A10&G1:G10&D110)*ROW(A1:A10)))

the formula must be entered with <Shift><Ctrl><Enter> instead of <Enter>,
also if edited later.

I'm sorry, but I don't understand the last part of your posting.

 

[Nick]

Thx for the help Leo

Nick

-----Original Message-----
Nick

It looks to me, that you need a formula like this array formula:
(A1:A10)))

the formula must be entered with <Shift><Ctrl><Enter>

 

[Leo Heuser]

You're welcome, Nick.

LeoH