Lookup Multiple Columns for Value to Input

[Kybred4492]

I have a spreadsheet of employees, with multiple positions, at multiple stores, and multiple pay rates, as below:

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]H[/TD]
[TD="width: 64"]John[/TD]
[TD="width: 64"]Cook[/TD]
[TD="class: xl58, width: 64, align: right"]10.00[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]John[/TD]
[TD]Porter[/TD]
[TD="class: xl58, align: right"]9.00[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Bob[/TD]
[TD]Cashier[/TD]
[TD="class: xl58, align: right"]8.00[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]Bob[/TD]
[TD]Cook[/TD]
[TD="class: xl58, align: right"]10.00[/TD]
[/TR]
</tbody>[/TABLE]

I want to use a lookup, that will take what I have in one table, which will be timecard records, and look in the second table, above, to look for matching location (column A), matching name (column B), matching position (column C), AND then use the rate (column D) if all of that matches. It should then pull that rate for that person, if all areas match, over to the timecard table.

If there is no exact match, then "False" could be the entry, meaning I do not have a rate for that person, positiion, and/or store.

I have tried several ways and still cannot get it to work out for me. Is there anyone interested in helping me with this?

Thanks

 

[skorpionkz]

[TABLE="width: 192"]
<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: white"]Locatioon[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Name[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Position[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Rate[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: white"]K[/TD]
[TD="class: xl65, bgcolor: white"]John[/TD]
[TD="class: xl65, bgcolor: white"]Porter[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[/TR]
</TBODY>[/TABLE]

i added this to kolumns F to I, but you can change it and adjust the formula:

=SUMPRODUCT((--(A1:A1000=F2))*(--(B1:B1000=G2))*(--(C1:C1000=H2))*D1:D1000)

 

[Kybred4492]

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: white"]Locatioon[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Name[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Position[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Rate[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: white"]K[/TD]
[TD="class: xl65, bgcolor: white"]John[/TD]
[TD="class: xl65, bgcolor: white"]Porter[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]

i added this to kolumns F to I, but you can change it and adjust the formula:

=SUMPRODUCT((--(A1:A1000=F2))*(--(B1:B1000=G2))*(--(C1:C1000=H2))*D1:D1000)

That formula, even with my making the changes and setting it up like you have, it does not work and returns a value error.