Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Question | Excel Discussion (Misc queries) | |||
Formula Question.....PLEASE PLEASE help! | Excel Discussion (Misc queries) | |||
Parsing Data w/ a Formula (another question) | Excel Worksheet Functions | |||
Formula Question | Excel Worksheet Functions | |||
SUM array formula question | Excel Worksheet Functions |