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!
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!