Index match fomrula error

[samitnair]

Hi

Am try to extract where the data intersects

Example: Match 315 (B2) 2 (C2) the value which intersects at M24...result must be Value in F5

I used index and match but am getting #ref

=IF(ISNA(INDEX('Domestic EP'!$D$4:$N$32,MATCH(Sheet3!B2,'Domestic EP'!$B$4:$B$32,FALSE),MATCH(Sheet3!C2,'Domestic EP'!$C$4:$C$32,FALSE),MATCH(Sheet3!D2,'Domestic EP'!$D$3:$N$3,FALSE))),"0",(INDEX('Domestic EP'!$D$4:$N$32,MATCH(Sheet3!B2,'Domestic EP'!$B$4:$B$32,FALSE),MATCH(Sheet3!C2,'Domestic EP'!$C$4:$C$32,FALSE),MATCH(Sheet3!D2,'Domestic EP'!$D$3:$N$3,FALSE))))

Thanks

 

[Aladin Akyurek]

Just taking up the exhibit and dropping the sheet name...

Z2, control+shift+enter, not just enter:

=INDEX($F$4:$F$24,MATCH(1,IF($B$2:$B$24=X2,IF($C$2:$C$24=Y2,1)),0))

where

X2 = 315

and

Y2 = 2

 

[samitnair]

Hi Aladin

Thank you for the quick reply....but i couldnt get ur concept or the way to implement it

I got 2 sheets
"Domestic EP" (data)
"Sheet3" (Result)

Scenario: If i enter 630,3, M24 in sheet3 B2,C2,D2 it must intersect these values in sheet "Domestic EP" and give result as 3311.....I have inserted a screen shot for better understanding

thanks

 

[Aladin Akyurek]

Hi Aladin

Thank you for the quick reply....but i couldnt get ur concept or the way to implement it

I got 2 sheets
"Domestic EP" (data)
"Sheet3" (Result)

Scenario: If i enter 630,3, M24 in sheet3 B2,C2,D2 it must intersect these values in sheet "Domestic EP" and give result as 3311.....I have inserted a screen shot for better understanding

...

Sheet3...

B2: 630
C2: 3
D2: M24

E2, control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
    INDEX('Domestic EP'!$D$4:$N$32,
     MATCH(1,
       IF('Domestic EP'!$B$4:$B$32=B2,
       IF('Domestic EP'!$C$4:$C$32=C2,1)),0),
     MATCH(D2,'Domestic EP'!$D$3:$N$3,0))))

 

[samitnair]

Hi

It worked perfectly fine and please excuse for the late response

But would u mind explaining the formula...I would understand for future reference


=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0, INDEX('Domestic EP'!$D$4:$N$32, MATCH(1, IF('Domestic EP'!$B$4:$B$32=B2, IF('Domestic EP'!$C$4:$C$32=C2,1)),0), MATCH(D2,'Domestic EP'!$D$3:$N$3,0))))</pre>
What does "9.99999999999999E+307" and "CHOOSE({1,2},0," do and is there any specific reason why the formula is given in different lines

 

[Aladin Akyurek]

Hi

It worked perfectly fine and please excuse for the late response

You are welcome.

But would u mind explaining the formula...I would understand for future reference



=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0, INDEX('Domestic EP'!$D$4:$N$32, MATCH(1, IF('Domestic EP'!$B$4:$B$32=B2, IF('Domestic EP'!$C$4:$C$32=C2,1)),0), MATCH(D2,'Domestic EP'!$D$3:$N$3,0))))
</PRE>
What does "9.99999999999999E+307" and "CHOOSE({1,2},0," do and is there any specific reason why the formula is given in different lines

Here a link on the big number, which you can shorten to 9.99E+307 if so desired:

http://www.mrexcel.com/forum/showthread.php?t=102091

LOOKUP with that big number as look up value will always pick out the last numeric value from a reference it is fed with. I explain the mechanics involved here:

http://www.mrexcel.com/forum/showthread.php?t=310278

in post #7.

The reference LOOKUP needs is constructed in the formula
under consideration by means of CHOOSE...

CHOOSE({1,2},0,formula expression)

creates an array constant consisting of two values: a 0 and the result from the formula expression, something like:

{0,3311}

{0,#N/A}

LOOKUP will return 3311 from the first array, 0 from the second.

Finally, using separate lines is just for display purposes. The formula can be
read without scrolling.