Matching table A with table B(Plz give a look, m unable to do it)

[livinggourmand]

Hi,
I have 2 table both have more than 10K rows.

I have to find which row in main table corresponds with row in Check table.
note the combination should match not just a column.
ex A25 n B25 (main table) matches with D2 n E2 (check table)
note the combination should match not just a column.

plz let me know if any clarification is required

Thanks

the sheet is uploaded at google doc as i was not able to upload it here
https://docs.google.com/spreadsheet/ccc?key=0AltIfHyzagUAdHBpMkhURlFSbTA0cFJucXpIR1BCQXc&hl=en_US#gid=0

 

[Aladin Akyurek]

the result is not right

Of course not. Failed to lock a part of a range in the formula...

=IFERROR(MATCH(E3,IF($A$3:$A$29=D3,$B$3:$B$29),0),"no match")

 

[livinggourmand]

it worked!!!!! :D
Thanks a lot

can u explain briefly how ur formula wrks
it will be a great help

and i appreciate ur quick response and perfect solution

 

[Aladin Akyurek]

it worked!!!!! :D
Thanks a lot

can u explain briefly how ur formula wrks
it will be a great help

IF($A$3:$A$29=D3,$B$3:$B$29)

tests whether A3:A29 is equal to the look up value in D3 and includes all of the corresponding B-values in the result array, something like...

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;
FALSE;92;FALSE;FALSE;FALSE;FALSE;FALSE}

MATCH(E3,result array,0)

matches the look up value in E3 against result array and reports the matches if successful. The error values (#N/A) are picked up by the outer IFERROR, leading to "no match".

and i appreciate ur quick response and perfect solution

You are welcome. Thanks for providing feedback.

 

[livinggourmand]

ok nice...
:D
got it !!!

thanks again

Match always returns row no??

 

[Aladin Akyurek]

ok nice...
:D
got it !!!

thanks again

You are welcome.

Match always returns row no??

MATCH(LookUpValue,A10:A25,0)

will return a 'row number' relative to A10:A25, running from 1 to 16, not 10 to 25.

MATCH(LookupValue,A:A,0)

or

MATCH(LookUpValue,A10:A25,0)+MIN(ROW(A10))-1

would yield the native row number.