lookup on Table including blanks -

[Guest]

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

 

[Domenic]

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!

 

[vezerid]

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

 

[Guest]

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...........

 

[Domenic]

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

 

[Guest]

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]

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?

 

[Lori]

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).