Lookup for a alphanumeric value in an Array

[roka]

Hi

I am trying to lookup for a alphanumeric value within a certain data range and if the value is found, I want to get the value from the corresponding row.
In the image attached, I would like to look for values that are in column A, within the range B2:C9. If the value is found I want the value from column D corresponding to the row in which the value was found.
For e.g. I want to look for value in A5 in the range B2:C9. The value is found in C3, therefore the value in the corresponding row in Column D which is the value in cell D3 will be printed.

I would really appreciate your help to find a solution.

Thanks

[martindwilson]

you can make use of iferror
=IFERROR(IFERROR(INDEX($D$2:$D$9,MATCH(A5,$B$2:$B$9,0)),INDEX($D$2:$D$9,MATCH(A5,$C$2:$C$9,0))),"not found")

[roka]

Thanks for that MartinDwilson.

I am trying that formula over 4 and 6 columns and excel throws me an error for having too many arguments. Is there a way around it?

Thanks

[martindwilson]

that's not what you asked originally is it!

[roka]

that's not what you asked originally is it!

I had assumed I would be able to replicate the formula over 4 columns.
My bad!

[martindwilson]

try this array entered formula
=INDEX(Sheet1!D1:D9,MAX(IF(Sheet1!B$1:C$9=A5,ROW(Sheet1!A$1:A$9))))