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

[MuhammadZiaUrRehman]

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.

Auction	Auction2	Realized	Weighted Average Yield (%)	Column3	Column4
No.	Date	Amount	3-Mon	6-Mon	12-Mon
1	24-Jun-98	23,623.24	Bids not Received	15.7033%	Bids not Received
2	13-Jul-98	7,708.23	14.6157%	15.4142%	16.0000%
3	22-Jul-98	2,305.36	Bids Rejected	15.7402%	Bids Rejected
4	4-Aug-98	21,261.33	13.82%	15.2905%	15.9000%
5	17-Aug-98	19,628.07	13.71%	14.9434%	15.7803%
6	3-Sep-98	20,526.84	Bids Rejected	13.8548%	15.3924%
7	28-Sep-98	9,255.20	Bids Rejected	13.5467%	15.1701%
8	12-Oct-98	1,910.04	Bids Rejected	Bids Rejected	15.1808%
9	22-Oct-98	Bids Rejected	Bids Rejected	Bids Rejected	Bids Rejected
10	3-Nov-98	23,149.34	Bids Rejected	Bids Rejected	14.1285%
11	16-Nov-98	6,981.53	9.5002%	11.9609%	12.9944%
12	3-Dec-98	5,998.96	Bids Rejected	11.9342%	12.9944%
13	22-Dec-98	2,207.54	Bids Rejected	11.8724%	12.9809%
14	9-Jan-99	5,310.86	Bids Rejected	12.1011%	12.9755%
15	20-Jan-99	8,364.98	12.1124%	12.4582%	13.1684%
16	1-Feb-99	20,217.27	12.4334%	12.8696%	13.5400%
17	13-Feb-99	15,086.10	12.7141%	13.2991%	13.8117%
18	3-Mar-99	10,978.54	12.3706%	12.9581%	Bids Rejected
19	25-Mar-99	3,860.06	10.7330%	11.3501%	11.8043%
20	21-Apr-99	211.15	Bids Rejected	10.5987%	11.4951%
21	12-May-99	Bids Rejected	Bids Rejected	Bids Rejected	Bids Rejected
22	26-May-99	2,449.45	8.3704%	Bids Rejected	Bids Rejected
23	9-Jun-99	10,210.51	7.5266%	Bids Rejected	Bids Rejected
24	23-Jun-99	12,580.09	6.6599%	Bids Rejected	10.0979%
25	7-Jul-99	2,063.78	Bids Rejected	Bids Rejected	10.3307%
26	21-Jul-99	6,403.10	6.9627%	Bids Rejected	10.2901%
27	11-Aug-99	15,569.91	6.9443%	Bids Rejected	10.1841%
28	25-Aug-99	9,919.75	7.8593%	9.4286%	10.1996%
29	8-Sep-99	17,856.24	8.7985%	10.0537%	10.3234%
30	22-Sep-99	5,428.64	8.9583%	10.2488%	10.7313%
31	6-Oct-99	2,187.07	Bids Rejected	10.3554%	Bids Rejected
32	20-Oct-99	Bids Rejected	Bids Rejected	Bids Rejected	Bids Rejected
33	3-Nov-99	26,708.09	8.7774%	9.9434%	10.6126%
34	17-Nov-99	5,884.40	8.8226%	10.1345%	10.8707%
35	1-Dec-99	1,742.63	8.8678%	10.1560%	10.7721%
36	15-Dec-99	1,833.81	Bids not Received	10.0972%	10.3281%
37	29-Dec-99	201.77	Bids not Received	Bids Rejected	10.3061%
38	12-Jan-00	95.94	Bids Rejected	8.4869%	Bids Rejected
39	26-Jan-00	2,719.69	8.0099%	8.4283%	8.9451%
40	9-Feb-00	8,746.78	7.5414%	7.9600%	8.4359%
41	23-Feb-00	2,642.95	7.2899%	7.4462%	7.8965%
42	8-Mar-00	71.35	Bids Rejected	Bids Rejected	7.8924%
43	22-Mar-00	870.03	7.2868%	7.4440%	7.8949%
44	5-Apr-00	8,318.59	7.1103%	7.1961%	7.6851%
45	19-Apr-00	4,674.80	6.9757%	7.1262%	7.5916%
46	3-May-00	20,762.14	6.9374%	7.0925%	7.5836%
47	17-May-00	2,263.23	6.9757%	7.2024%	7.6172%
48	31-May-00	1,198.66	6.9533%	7.2178%	7.6086%
49	14-Jun-00	984.3	6.9308%	Bids Rejected	Bids Rejected
50	28-Jun-00	Bids Rejected	Bids Rejected	Bids Rejected	Bids Rejected
51	12-Jul-00	8,750.29	6.8486%	7.1376%	Bids Rejected
52	26-Jul-00	7,780.42	7.0302%	7.2339%	7.7771%
53	9-Aug-00	698.65	Bids Rejected	7.3816%	7.9595%
54	23-Aug-00	1,520.62	Bids Rejected	7.3816%	8.0122%
55	6-Sep-00	255.08	Bids Rejected	7.4462%	8.1039%
56	20-Sep-00	11,834.92	7.9522%	8.1397%	8.9253%
57	4-Oct-00	11,036.69	10.2269%	10.4684%	10.9098%
58	18-Oct-00	189.6	Bids Rejected	11.0006%	Bids Rejected
59	1-Nov-00	12,790.27	Bids Rejected	10.9923%	11.4789%
60	15-Nov-00	426.69	Bids Rejected	10.9560%	Bids Rejected
61	29-Nov-00	993.03	Bids not Received	10.9225%	11.4893%
62	13-Dec-00	585.28	Bids not Received	10.9560%	11.4893%

 

[maabadi]

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:

=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.

 

[MuhammadZiaUrRehman]

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:

=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.

 

[AlanY]

try add the D3 like this

=IF(OR(D3="Bids Rejected",D3="Bids not Received"),D2,D3)

 

[maabadi]

Sorry my fault. Try AlanY formula.

 

[MuhammadZiaUrRehman]

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.

 

[maabadi]

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.