Lookup/Index formula question.
 

I am trying to say if column E and column F match, then go to the Plan
Information sheet and get the corresponding value in column C.

For Example, in the first sheet I have:

E F G
Plan Elevation Square Ft.
Ariza 11 ?
Ariza 23 ?
Ariza 24 ?
Brigotta 11 ?
Brigotta 23 ?


On the Plan Information tab I have:

A B C
Plan Elev Square Footage
Amici 32 1336
Andorra 32 3564
Andorra 44 3564
Anguilla 11 3772
Anguilla 19 3772
Anguilla 30 3772
Anguilla 32 3772
Anguilla 44 3772
Antigua 11 3341
Antigua 23 3320
Antigua 25 3341
Arezzo 32 1493
Ariza 11 3206
Ariza 23 3206
Ariza 24 3206
Avilla 11 4581
Avilla 23 4581
Brigotta 11 3666
Brigotta 23 3666
Brigotta 24 3666


This is the formula I am trying to use but keep getting NA error.

=INDEX('Plan Information'!C2:C256,MATCH(5,('Plan
Information'!$A$1:$A$1000=E$39)*('Plan Information'!$B$1:$B$1000=$F39),0))

Any suggestions?

Hi
use the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX('plan'!$C$1:$C$100,MATCH(1,('plan'!$A$1:$A$ 100=E1)*('plan'!$B$1:
$B$100=F1),0))

--
Regards
Frank Kabel
Frankfurt, Germany

"Barbara" schrieb im Newsbeitrag
...
I am trying to say if column E and column F match, then go to the
Plan
Information sheet and get the corresponding value in column C.

For Example, in the first sheet I have:

E F G
Plan Elevation Square Ft.
Ariza 11 ?
Ariza 23 ?
Ariza 24 ?
Brigotta 11 ?
Brigotta 23 ?


On the Plan Information tab I have:

A B C
Plan Elev Square Footage
Amici 32 1336
Andorra 32 3564
Andorra 44 3564
Anguilla 11 3772
Anguilla 19 3772
Anguilla 30 3772
Anguilla 32 3772
Anguilla 44 3772
Antigua 11 3341
Antigua 23 3320
Antigua 25 3341
Arezzo 32 1493
Ariza 11 3206
Ariza 23 3206
Ariza 24 3206
Avilla 11 4581
Avilla 23 4581
Brigotta 11 3666
Brigotta 23 3666
Brigotta 24 3666


This is the formula I am trying to use but keep getting NA error.

=INDEX('Plan Information'!C2:C256,MATCH(5,('Plan
Information'!$A$1:$A$1000=E$39)*('Plan
Information'!$B$1:$B$1000=$F39),0))

Any suggestions?

Try:

=INDEX('Plan Information'!$C$2:$C$256,MATCH(E39&F39,'Plan
Information'!$A$2:$A$256&'Plan Information'!
$B$1:$B$256,0))

Array-entered.

HTH
Jason
Atlanta, GA

-----Original Message-----
I am trying to say if column E and column F match, then
go to the Plan
Information sheet and get the corresponding value in
column C.

For Example, in the first sheet I have:

E F G
Plan Elevation Square Ft.
Ariza 11 ?
Ariza 23 ?
Ariza 24 ?
Brigotta 11 ?
Brigotta 23 ?


On the Plan Information tab I have:

A B C
Plan Elev Square Footage
Amici 32 1336
Andorra 32 3564
Andorra 44 3564
Anguilla 11 3772
Anguilla 19 3772
Anguilla 30 3772
Anguilla 32 3772
Anguilla 44 3772
Antigua 11 3341
Antigua 23 3320
Antigua 25 3341
Arezzo 32 1493
Ariza 11 3206
Ariza 23 3206
Ariza 24 3206
Avilla 11 4581
Avilla 23 4581
Brigotta 11 3666
Brigotta 23 3666
Brigotta 24 3666


This is the formula I am trying to use but keep getting
NA error.

=INDEX('Plan Information'!C2:C256,MATCH(5,('Plan
Information'!$A$1:$A$1000=E$39)*('Plan Information'!
$B$1:$B$1000=$F39),0))

Any suggestions?

.