How can I match multiple column and return a value

[mekcsx]

[TABLE="width: 291"]
<TBODY>[TR]
[TD="colspan: 4"]I want to write a macro that match multiple column and return a value as I show in the table below</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Plant </SPAN>
[/TD]
[TD]SLOC</SPAN>
[/TD]
[TD]Grade</SPAN>
[/TD]
[TD]Posting Date</SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD]555</SPAN>
[/TD]
[TD]abc</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD]777</SPAN>
[/TD]
[TD]def</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD]666</SPAN>
[/TD]
[TD]jkl</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5</SPAN>
[/TD]
[TD]999</SPAN>
[/TD]
[TD]asd</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]--------[/TD]
[TD]-------[/TD]
[TD]--------[/TD]
[TD]------------[/TD]
[/TR]
[TR]
[TD]Material </SPAN>
[/TD]
[TD]Plant</SPAN>
[/TD]
[TD]SLOC</SPAN>
[/TD]
[TD]Posting Date</SPAN>
[/TD]
[/TR]
[TR]
[TD]asd</SPAN>
[/TD]
[TD]5</SPAN>
[/TD]
[TD]999</SPAN>
[/TD]
[TD]5/5/2013</SPAN>
[/TD]
[/TR]
[TR]
[TD]jkl</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[TD]666</SPAN>
[/TD]
[TD]2/2/2013</SPAN>
[/TD]
[/TR]
[TR]
[TD]def</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[TD]777</SPAN>
[/TD]
[TD]3/3/2013</SPAN>
[/TD]
[/TR]
[TR]
[TD]abc</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[TD]555</SPAN>
[/TD]
[TD]1/1/2013</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]I have to check multiple column from 2sources and get the Posting Date from Table2 to Table1. I'm quite confused about the function which I should use like match, lookup, index, etc. Can you show how can I solve this problem. Thank you</SPAN>


[/TD]
[/TR]
</TBODY>[/TABLE]

 

[raj2206]

Hey Thanks for Writing!!

Say I have the Table 1 in the range : A1:D5 and Table 2 in the range A8:D12

Now In the cell D2 paste the following formula and drag the formula down:

=INDEX($D$9:$D$12,SUMPRODUCT(((A2=$B$9:$B$12)*(B2=$C$9:$C$12)*(C2=$A$9:$A$12))*(ROW($A$9:$A$12)-ROW($A$9)+1)))

Change the referencing as per your original data (the logic would be the same)

Hope this helps!!

Thanks/ Raj

 

[mekcsx]

Hey Thanks for Writing!!

Say I have the Table 1 in the range : A1:D5 and Table 2 in the range A8:D12

Now In the cell D2 paste the following formula and drag the formula down:

=INDEX($D$9:$D$12,SUMPRODUCT(((A2=$B$9:$B$12)*(B2=$C$9:$C$12)*(C2=$A$9:$A$12))*(ROW($A$9:$A$12)-ROW($A$9)+1)))

Change the referencing as per your original data (the logic would be the same)

Hope this helps!!

Thanks/ Raj

Thanks for your help,
However, I got an error "#VALUE". Could you please briefly explain about this solving concept?
and maybe I can fix it.

thanks in advance for your kindly help

 

[raj2206]

Hi,

I am getting all the correct values in my sheet (as per the direction), also please note that there is an extra space in the value: asd in the Table 2, fix that and u will get all the answers. Also you need to format the cell values as Date, as the answer returns the date serial number.

Hope this helps.

Raj

 

[mekcsx]

I try so much to fix this problem because I'm new for excel. Finally, I got it.
Thanks a lot for your help & also this website as well