PDA

View Full Version : Values lookup in two different tables



lakers617
05-27-2016, 11:03 AM
Gurus,
Need your help. Hope someone can show me how solve this.

Sample data
Sheet1



L
M(weight)
N(amount)
O(dollars)


F1
2 lb
10 dollars
4


F2
1 lb
5 dollars
10





Sheet2



A
B
C
D
E
F


F1
10
100
25
2 lb
2 dollars per lb


F2
10
100
30
3lb
3 dollars per lb




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!

lakers617
05-27-2016, 01:18 PM
Still hoping and waiting for someone to reply

This is my excel
Column O needs to be filled with above logic.
Column R has values for formula verification

mdmackillop
05-28-2016, 04:22 AM
I'm not getting your results but the code is simple and I'll leave you to identify the logic error. All on one sheet for simplicity.