lookup on Table including blanks -
 

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

lookup on Table including blanks -
 

Assumptions:

A2:C11 contains the data

The pattern in Column B is consistent, with the same number of cells and
in the same order for each value in Column A

Formula:

=INDEX(C2:C11,MATCH(E2,A2:A11,0)+MATCH(F2,B2:B6,0)-1)

....where E2 contains the first criteria, such as AAA, and F2 contains
the second criteria, such as SST.

Hope this helps!

In article ,
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

lookup on Table including blanks -
 

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:A1000)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

lookup on Table including blanks -
 

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:A1000)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

lookup on Table including blanks -
 

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

lookup on Table including blanks -
 

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

lookup on Table including blanks -
 

Unfortunately, it's not clear to me what you'd like to do. In which way
would you like to "consider cells between 'Eur' & 'Asia'? Can you
describe, step by step, the process involved in reaching the desired
result?

In article ,
Nir wrote:

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?

lookup on Table including blanks -
 

I've come across this before when looking up data in PivotTables.
Referring to the data above, the key to the formula is:

LOOKUP(ROW(1:6),ROW(1:6)/(A1:A6<""),A1:A6)

which fills in the blanks in the first column. Then combine with the
other conditions

=LOOKUP(2,1/(B1:B6="France")/(LOOKUP(ROW(1:6),ROW(1:6)/(A1:A6<""),A1:A6)="EUR"),C1:C6)

which returns "Paris" (it should be OK without array entry).


Nir wrote:

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