Values lookup in two different tables

[lakers617]

Gurus,
Need your help. Hope someone can show me how solve this.

Sheet1
Capture1.jpg


Sheet2
Capture2.jpg



This is similar to VLOOKUP

LOGIC:
-----------------------------
Check if L1 value matches any cell value in Column A
If it find hits, for that hit row, check
if Column E >= M, take value from F, multiply with M1 and then put in O1 (=cell corresponding to L1 row)
else, check if Column C<=N, then put value from B into O1
else, take value from D and put into O1

else fill with 'no value found'

Similarly next I have to pick L2 and check for a hit in Column A put that final value in O2 (= cell corresponding to L2 row), etc
------------------------------
There are 1000 of rows in First table. So I have to run this formula or macro for all the row and fill Column O.

I have data for each table in separate worksheet: Sheet 1 and Sheet 2.
If someone can give a (vlookup) formula or macro, that would be very very helpful. I can run for column O.

Thanks a lot!

[JeteMc]

Welcome to the forum. Please attach a sample of the workbook from which the two images were made. Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[lakers617]

Here is the file.

Column O should be filled based on my logic.
I put the expected value in Column R for your verification.

thanks a lot

[JeteMc]

I don't understand why the expected value in R2 is 10. To me it appears that according to the logic from post #1 "if Column E >= M, take value from F, multiply with M1 and then put in O1 (=cell corresponding to L1 row)" which becomes be IF 150>21 THEN 0*21 the value in R2 (O2) should be 0.

[JeteMc]

Here is a formula, based on the logic from post #1, that can be pasted in O2 and double clicked (fill handle) so as to copy down as far as needed:

Please Login or Register  to view this content.

Notice that most of the values yielded by the formula do not match values given in column R.

[newdoverman]

There is something wrong somewhere. JeteMc's formula follows the logic of the first message perfectly as far as I can tell. That being the case, the values in column R cannot be correct. Please confirm both the logic, using the actual cell references in the workbook and the expected values that are in column R.