getting a formula to do a similar job as another

L

LearningEX

Board Regular
Joined
Mar 13, 2015
Messages
208
i would like the following formula (1) and (2) to work in the same way as formula (3)

formula (3)

Code: 
=VLOOKUP(Agency_Daily_Input!D9,Agency_Contract!$C$9:$T$10000,IF(AND(WEEKDAY(Agency_Daily_Input!B9)>1,WEEKDAY(Agency_Daily_Input!B9)<7),13,IF(WEEKDAY(Agency_Daily_Input!B9)=7,15,IF(WEEKDAY(Agency_Daily_Input!B9)=1,17)+IF(Agency_Daily_Input!G9>0.708333333333333,1))),FALSE)

the above one return a value based on cell c9 on Agency Daily Input

id like formula (2) to do the same

Code: 
=IF(Agency_Contract!X9:X100000=0,0,IF(J9>Agency_Contract!X9:X100000,J9-Agency_Contract!X9:X100000,0))

formula (3)

Code: 
=T9*Agency_Contract!$Y$9:$Y$100000*I9
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
This looks very like your other query that is still live, you aren't spamming your question now are you? It's generally frowned upon.
 
Upvote 0
i could not delete the other one and i thought this one explained things better. do you know how i can delete the other post
 
Upvote 0
you can't delete posts, moderator might move it though
 
Upvote 0
To be honest it's probably more confusing since that first one has a number of parts that the others don't come close to performing.

Your main issue however exists in this one:

Code: 
=T9*Agency_Contract!$Y$9:$Y$100000*I9

In that:

"Agency_Contract!$Y$9:$Y$100000"

That bit bears no relevance to the rest of the formula. This is a reference to a range of cells and you must consolidate it into a single value in some way in order to multiply it againts the other cell references in your formula.

I can only guess this is the part you think should be a VLOOKUP.
 
Upvote 0
TheGlovner said:
To be honest it's probably more confusing since that first one has a number of parts that the others don't come close to performing.

Your main issue however exists in this one:

Code: 
=T9*Agency_Contract!$Y$9:$Y$100000*I9

In that:

"Agency_Contract!$Y$9:$Y$100000"

That bit bears no relevance to the rest of the formula. This is a reference to a range of cells and you must consolidate it into a single value in some way in order to multiply it againts the other cell references in your formula.

I can only guess this is the part you think should be a VLOOKUP.
Click to expand...

yes the two small codes
 
Upvote 0
TheGlovner said:
I'm not sure what you mean by that, but I'm convinced by your statements that you haven't grasped how the vlookup function actually works, read the following before going any further:

https://support.office.com/en-in/article/VLOOKUP-function-adceda66-30de-4f26-923b-7257939faa65
Click to expand...

if cell D1 = Nampak1 then Cells I and M need to look up cells on Agency Contract and retrieve information from the row that has Nampak1 and put it into I and M, its abit like what the long formula is doing just slightly different
 
Upvote 0
need to incorporate

Code: 
Agency_Daily_Input!D9

into both of the smaller formulas
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
233
Eric W
Eric W
L
  • Solved
Looking for an sequence of consecutive days within an array of dates while ignoring weekends
Replies
6
Views
571
Anonymous1378
A
M
  • Solved
Update formula on mouse click without macro
Replies
3
Views
204
Fluff
Fluff
gtd526
  • Solved
Average specific value if listed on any worksheet
Replies
7
Views
379
Tetra201
Tetra201
gtd526
  • Question
vba Sort with Criteria
Replies
1
Views
211
gtd526
gtd526

Forum statistics

Threads
1,214,584
Messages
6,120,385
Members
448,956
Latest member
JPav

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top