HLOOKUP with named range 1

[deedar]

In an Excel sheet (Excel 2003), I have the following columns:

Account Code
Identifier
Debit
Credit
Debit (U1-U3)
Credit (R1-R3)
Debit (U4-U8,R4-R8)
Credit (U9-U10,R9-R10)
U1
U2
… Up to
U10
R1
R2
… Up to
R10

Column B (Identifier) contains one of the code from U1,U2, …U10,R1,R2, …,R10

I have to fill columns E,F,G,H as follows:

E: To show Debit amount from column C if identifier is U1,U2 or U3
F: To show Credit amount from column D if identifier is R1,R2 or R3
G: To show Debit amount from column C if identifier is U4 to U8 or R4 to R8
H: To show Credit amount from column D if identifier is U9,U10,R9 or R10

I created 4 named ranges as follows:

Asset Refers to Sheet1!$I$2:$K$2 (Column headings begin from Row 2)
Liability Refers to Sheet1!$S$2:$U$2
Income Refers to Sheet1!$L$2:$P$2,Sheet1!$V$2:$Z$2
Expense Refers to Sheet1!$Q$2:$R$2,Sheet1!$AA$2:$AB$2

Then I inserted the following formulas in cells E3, F3, G3, H3 and copied it down:

E3 =IF(ISNA(HLOOKUP($B3,Asset,1,0)),"",$C3)
F3 =IF(ISNA(HLOOKUP($B3,Liability,1,0)),"",$D3)
G3 =IF(ISNA(HLOOKUP($B3,Income,1,0)),"",$C3)
H3 =IF(ISNA(HLOOKUP($B3,Expense,1,0)),"",$D3)

Formula works well when there is a single range specified in Refers to, as in E3, F3. But it is not working for cells G3 and H3.

 

[SkipVought]

Hi,

I think that the problem is in the RANGE defintion of...
[tt]
Income Refers to Sheet1!$L$2:$P$2,Sheet1!$V$2:$Z$2
Expense Refers to Sheet1!$Q$2:$R$2,Sheet1!$AA$2:$AB$2
[/tt]
these are not contiguous ranges. You may need to break it up like this...
[tt]
Income1 Refers to Sheet1!$L$2:$P$2
Income2 Refers to Sheet1!$V$2:$Z$2
Expense1 Refers to Sheet1!$Q$2:$R$2
Expense2 Refers to Sheet1!$AA$2:$AB$2
[/tt]


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[deedar]

Thanks Skip.

I deleted Income and Expense names and added Income1, Income2, Expense1 and Expense2.

But now, how can I modify the HLOOKUP formula to accommodate both ranges;

G3 =IF(ISNA(HLOOKUP($B3,[Income1,Income2??],1,0)),"",$C3)

 

[SkipVought]

if found in EITHER then...
[tt]
G3 =IF(OR(ISNA(HLOOKUP($B3,[Income1],1,0)),ISNA(HLOOKUP($B3,[Income2],1,0))),"",$C3)+G3
[/tt]

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[deedar]

Thanks Skip.

The following worked in my scenario:

=IF(AND(ISNA(HLOOKUP($B3,Income1,1,0)),ISNA(HLOOKUP($B3,Income2,1,0))),"",$C3)