A B C
Eur England London
France Paris
Italy Rome
Asia India Delhi
China Beijing
Thailand Bangkok

I want to create a lookup that will consider cells between "Eur" & "Asia" as
Eur.

is it understandable?

"Domenic" wrote:

Can you provide an example, along with the actual result you expect?

In article ,
Nir wrote:

Vezerid,
It is not the case (i think)
I need condition set on A3&B3 where A3 can be blank, if blance i wish
formula to consider first value in A clumn from above. so if I perform
sumproduct on A&B i will always get value.

It can be done manually by filling up the blanks in coulmn A with 1st value
in the first blanck range and 2nd value in the 2nd blank range etc...........



"vezerid" wrote:

Nir,

If I understand correctly, you want to specify AAA and then you want
the formula to ignore column B and give you the sum of all AAA. If so,
and assuming your data is in A1:A1000, you can use the following
*array* formula:

=SUM(INDIRECT("C"&MATCH(E1,A1:A1000,0)&":C"&MIN(IF ((A1:A1000<"")*(ROW(A1:A1
000)MATCH(E1,A1:A1000,0)),ROW(A1:A1000)))-1))

Here E1 holds the key value of column A. Array formulas are committed
with the combination of Shift+Ctrl+Enter.

One note: the formula is designed with the (probably valid) assumption
that the key value will not appear in any other context. Thus, if you
modify the formula, change the bottom part of the ranges (e.g.
A1:A2000) but don't change the top part (let them all start from A1).
Of course you can change the columns.

HTH
Kostis Vezerides


Nir wrote:
Hi,
I have 3 columns Table on the A column there values with blancks between,
column B:C are with values.

I would like to sumproduct on A*B columns, evaluating A cloumn with the
up
nearest value.
See below row1:5 should go with "AAA" row6 on should go with "BBB"

A B C
AAA IIW 16
DCUT 20
SST 55
ST 44
UAT 14
BBB IIW 6
DCUT 40
SST 12
ST 8
UAT 14