How to Vlookup for a value in a table on a condition

MuhammadZiaUrRehman

New Member
Joined
May 14, 2016
Messages
9
I have a table of interest rates based on dates and maturity that I have attached . I want to vlookup a value (interest rates) based on the table based on the date (for due date). That table provides. However, in the table, in multiple places, instead of Value (interest rate), text is written like "Bids not received", or "Bid Rejected". Since, there is no new rate, then previous rate should apply, so I want a formula with which if there is such line written like "Bids not received", or "Bid Rejected", then it should get the interest rate value from the previous cell in which there was an interest rate. It may be easy to do for one cell only. The rate has to be selected for Date but if on that date no interest rate was there, then it should select for that date the previous cell (rate) in which there was the rate as on due date, then that rate will apply.

Sample Table is attached for reference. Please if someone can help me with this, it will automate a lot of work for me. Thank you. Feel Free to ask if some confusion is there.

AuctionAuction2RealizedWeighted Average Yield (%)Column3Column4
No.DateAmount3-Mon6-Mon12-Mon
124-Jun-9823,623.24Bids not Received15.7033%Bids not Received
213-Jul-987,708.2314.6157%15.4142%16.0000%
322-Jul-982,305.36Bids Rejected15.7402%Bids Rejected
44-Aug-9821,261.3313.82%15.2905%15.9000%
517-Aug-9819,628.0713.71%14.9434%15.7803%
63-Sep-9820,526.84Bids Rejected13.8548%15.3924%
728-Sep-989,255.20Bids Rejected13.5467%15.1701%
812-Oct-981,910.04Bids RejectedBids Rejected15.1808%
922-Oct-98Bids RejectedBids RejectedBids RejectedBids Rejected
103-Nov-9823,149.34Bids RejectedBids Rejected14.1285%
1116-Nov-986,981.539.5002%11.9609%12.9944%
123-Dec-985,998.96Bids Rejected11.9342%12.9944%
1322-Dec-982,207.54Bids Rejected11.8724%12.9809%
149-Jan-995,310.86Bids Rejected12.1011%12.9755%
1520-Jan-998,364.9812.1124%12.4582%13.1684%
161-Feb-9920,217.2712.4334%12.8696%13.5400%
1713-Feb-9915,086.1012.7141%13.2991%13.8117%
183-Mar-9910,978.5412.3706%12.9581%Bids Rejected
1925-Mar-993,860.0610.7330%11.3501%11.8043%
2021-Apr-99211.15Bids Rejected10.5987%11.4951%
2112-May-99Bids RejectedBids RejectedBids RejectedBids Rejected
2226-May-992,449.458.3704%Bids RejectedBids Rejected
239-Jun-9910,210.517.5266%Bids RejectedBids Rejected
2423-Jun-9912,580.096.6599%Bids Rejected10.0979%
257-Jul-992,063.78Bids RejectedBids Rejected10.3307%
2621-Jul-996,403.106.9627%Bids Rejected10.2901%
2711-Aug-9915,569.916.9443%Bids Rejected10.1841%
2825-Aug-999,919.757.8593%9.4286%10.1996%
298-Sep-9917,856.248.7985%10.0537%10.3234%
3022-Sep-995,428.648.9583%10.2488%10.7313%
316-Oct-992,187.07Bids Rejected10.3554%Bids Rejected
3220-Oct-99Bids RejectedBids RejectedBids RejectedBids Rejected
333-Nov-9926,708.098.7774%9.9434%10.6126%
3417-Nov-995,884.408.8226%10.1345%10.8707%
351-Dec-991,742.638.8678%10.1560%10.7721%
3615-Dec-991,833.81Bids not Received10.0972%10.3281%
3729-Dec-99201.77Bids not ReceivedBids Rejected10.3061%
3812-Jan-0095.94Bids Rejected8.4869%Bids Rejected
3926-Jan-002,719.698.0099%8.4283%8.9451%
409-Feb-008,746.787.5414%7.9600%8.4359%
4123-Feb-002,642.957.2899%7.4462%7.8965%
428-Mar-0071.35Bids RejectedBids Rejected7.8924%
4322-Mar-00870.037.2868%7.4440%7.8949%
445-Apr-008,318.597.1103%7.1961%7.6851%
4519-Apr-004,674.806.9757%7.1262%7.5916%
463-May-0020,762.146.9374%7.0925%7.5836%
4717-May-002,263.236.9757%7.2024%7.6172%
4831-May-001,198.666.9533%7.2178%7.6086%
4914-Jun-00984.36.9308%Bids RejectedBids Rejected
5028-Jun-00Bids RejectedBids RejectedBids RejectedBids Rejected
5112-Jul-008,750.296.8486%7.1376%Bids Rejected
5226-Jul-007,780.427.0302%7.2339%7.7771%
539-Aug-00698.65Bids Rejected7.3816%7.9595%
5423-Aug-001,520.62Bids Rejected7.3816%8.0122%
556-Sep-00255.08Bids Rejected7.4462%8.1039%
5620-Sep-0011,834.927.9522%8.1397%8.9253%
574-Oct-0011,036.6910.2269%10.4684%10.9098%
5818-Oct-00189.6Bids Rejected11.0006%Bids Rejected
591-Nov-0012,790.27Bids Rejected10.9923%11.4789%
6015-Nov-00426.69Bids Rejected10.9560%Bids Rejected
6129-Nov-00993.03Bids not Received10.9225%11.4893%
6213-Dec-00585.28Bids not Received10.9560%11.4893%
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
In the New empty Column at the same row Write:
For column Amount , if first cell is C3 and If your Empty Column is H3, then Write at H3:
Excel Formula:
=IF(OR(D3="Bids Rejected","Bids not Received"),D2,D3)
Then drag it right ( to number columns you want replace) and then drag it down.
Finally copy all this cells and Paste values at your source cells.
 
Upvote 0
In the New empty Column at the same row Write:
For column Amount , if first cell is C3 and If your Empty Column is H3, then Write at H3:
Excel Formula:
=IF(OR(D3="Bids Rejected","Bids not Received"),D2,D3)
Then drag it right ( to number columns you want replace) and then drag it down.
Finally copy all this cells and Paste values at your source cells.
It is giving value error with me. Did it work with you.
 
Upvote 0
try add the D3 like this

=IF(OR(D3="Bids Rejected",D3="Bids not Received"),D2,D3)
 
Upvote 0
Solution
try add the D3 like this

=IF(OR(D3="Bids Rejected",D3="Bids not Received"),D2,D3)
Thank you, it is working in the sense that i had to apply it to 6 or 7 times in every column to get the desired result.
However, can just one formula inside a vlookup not do the same trick, because right now the original Table will be Modified.
Once again thanks for the help.
 
Upvote 0
No Problem with one formula. after input it at first cell at first column, drag it right to columns you need (or copy formula with "=" sign, if columns seperated) then drag it down.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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