Formula Help

[Sasikiran]

Dear,

I need a formula to get the number appearing in column A if the text value
in column B & C is same as required.

Column A Column B Column C
134234 ABC John
253454 BCD John
234534 CDE John
453553 DEF John
345345 ABC Mary
453453 BCD Mary
3342342 CDE Mary
343543 DEF Mary
476566 ABC Peter
4564 67 BCD Peter
4534534 CDE Peter
3452342 DEF Peter
5766575 ABC Simon
564575 BCD Simon
345345 CDE Simon
23423 DEF Simon



ABC BCD CDE DEF
John =formula
Mary
Peter
Simon

Would require the corresponding number value in column A if the text value
in B &C columns is John & ABD.

Please help

 

[Lars-Åke Aspelin]

Dear,

I need a formula to get the number appearing in column A if the text value
in column B & C is same as required.

Column A Column B Column C
134234 ABC John
253454 BCD John
234534 CDE John
453553 DEF John
345345 ABC Mary
453453 BCD Mary
3342342 CDE Mary
343543 DEF Mary
476566 ABC Peter
4564 67 BCD Peter
4534534 CDE Peter
3452342 DEF Peter
5766575 ABC Simon
564575 BCD Simon
345345 CDE Simon
23423 DEF Simon



ABC BCD CDE DEF
John =formula
Mary
Peter
Simon

Would require the corresponding number value in column A if the text value
in B &C columns is John & ABD.

Please help

If your table with is located with ABC in cell F1 and thus John in
Cell E2, then try the following formula in cell F2:

=INDEX($A$1:$A$16,MATCH(1,(($B$1:$B$16=F$1)*($C$1:$C$16=$E2)),0))

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Then copy the formula to the right, until cell I2 (just below DEF).
Then copy cells F2 to I2 down until row 5 (to the right of Simon.

Now the tabel should be filled with the expected data.

Hope this helps / Lars-Åke