How can I match multiple column and return a value

[mekcsx]

I want to write a macro that match multiple column and return a value as I show in the table below</SPAN>
Plant </SPAN>	SLOC</SPAN>	Grade</SPAN>	Posting Date</SPAN>
1</SPAN>	555</SPAN>	abc</SPAN>
3</SPAN>	777</SPAN>	def</SPAN>
2</SPAN>	666</SPAN>	jkl</SPAN>
5</SPAN>	999</SPAN>	asd</SPAN>
--------	-------	--------	------------
Material </SPAN>	Plant</SPAN>	SLOC</SPAN>	Posting Date</SPAN>
asd</SPAN>	5</SPAN>	999</SPAN>	5/5/2013</SPAN>
jkl</SPAN>	2</SPAN>	666</SPAN>	2/2/2013</SPAN>
def</SPAN>	3</SPAN>	777</SPAN>	3/3/2013</SPAN>
abc</SPAN>	1</SPAN>	555</SPAN>	1/1/2013</SPAN>
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>

<TBODY>
</TBODY>

 

[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