Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP - WISH TO SUBSTITUTE FALSE FOR A VALUE
I've been playing around with an IF Function and nesting a VLOOKUP without
any luck. I've got a table which has a list of post codes with corresponding values against each. In a separate worksheet I have a list of post codes and where there are post codes the VLOOKUP function works and finds the value; however there are circumstances where the post codes are different and I rather than produce a FALSE statement and put N/A in the column I want it to add a different numerical value. I do not know all the post codes so I cannot add these to the VLOOKUP table. I need the VLOOKUP table to record the exact values that correspond to the post codes. HELP Please -- David |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP - WISH TO SUBSTITUTE FALSE FOR A VALUE
Try
=IF(ISNA(VLOOKUP(D1,A1:B100,2,FALSE)),"Your different value",VLOOKUP(D1,A1:B100,2,FALSE)) Regards, Alan. "David" wrote in message ... I've been playing around with an IF Function and nesting a VLOOKUP without any luck. I've got a table which has a list of post codes with corresponding values against each. In a separate worksheet I have a list of post codes and where there are post codes the VLOOKUP function works and finds the value; however there are circumstances where the post codes are different and I rather than produce a FALSE statement and put N/A in the column I want it to add a different numerical value. I do not know all the post codes so I cannot add these to the VLOOKUP table. I need the VLOOKUP table to record the exact values that correspond to the post codes. HELP Please -- David |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP - WISH TO SUBSTITUTE FALSE FOR A VALUE
Alan,
Thank you so much for your help. Your suggested formula was almost right for what I wanted. Rather than use FALSE I needed to substitute this for TRUE. If I hadn't received your input I would have been here until next year! Thanks once again as this is going to save me considerable time on a project I'm working on. -- David "Alan" wrote: Try =IF(ISNA(VLOOKUP(D1,A1:B100,2,FALSE)),"Your different value",VLOOKUP(D1,A1:B100,2,FALSE)) Regards, Alan. "David" wrote in message ... I've been playing around with an IF Function and nesting a VLOOKUP without any luck. I've got a table which has a list of post codes with corresponding values against each. In a separate worksheet I have a list of post codes and where there are post codes the VLOOKUP function works and finds the value; however there are circumstances where the post codes are different and I rather than produce a FALSE statement and put N/A in the column I want it to add a different numerical value. I do not know all the post codes so I cannot add these to the VLOOKUP table. I need the VLOOKUP table to record the exact values that correspond to the post codes. HELP Please -- David |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP - WISH TO SUBSTITUTE FALSE FOR A VALUE
You're welcome,
Regards, Alan. "David" wrote in message ... Alan, Thank you so much for your help. Your suggested formula was almost right for what I wanted. Rather than use FALSE I needed to substitute this for TRUE. If I hadn't received your input I would have been here until next year! Thanks once again as this is going to save me considerable time on a project I'm working on. -- David "Alan" wrote: Try =IF(ISNA(VLOOKUP(D1,A1:B100,2,FALSE)),"Your different value",VLOOKUP(D1,A1:B100,2,FALSE)) Regards, Alan. "David" wrote in message ... I've been playing around with an IF Function and nesting a VLOOKUP without any luck. I've got a table which has a list of post codes with corresponding values against each. In a separate worksheet I have a list of post codes and where there are post codes the VLOOKUP function works and finds the value; however there are circumstances where the post codes are different and I rather than produce a FALSE statement and put N/A in the column I want it to add a different numerical value. I do not know all the post codes so I cannot add these to the VLOOKUP table. I need the VLOOKUP table to record the exact values that correspond to the post codes. HELP Please -- David |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP - WISH TO SUBSTITUTE FALSE FOR A VALUE
Alan,
A false dawn I'm afraid. I thought I'd cracked it with your help, but when I copied the function throughout the spreadsheet it has returned incorrect values. With the TRUE added it seems to pick the nearest result to the post code within the VLOOKUP Table, whereas when I use your formula i.e. FALSE it returns the correct value for the first entry, but again a problem occurrs when you copy the formula throughout the spreadsheet. In this case it returns the same value throughout the pasted items. To give you a further snapshot of what I have: - 1st Worksheet Under Column J I have the first 3 arguments of the postcode which is say AB25 2nd Worksheet This is where the vlookup table is held; and the values are listed in alphabetical order. Column A Column B Row 1 Post Code Percentage Row 2 AB25 0.002% Row 3 AB26 0.05% Row 4 EC1 0.075% I have added your formula to a separate column in the 1st Worksheet e.g. =IF(ISNA(VLOOKUP(J2,($A$2:$B$4,2,FALSE)),0.50%,VLO OKUP(J2,$A$2:$B$4,2,FALSE)) In this example 0.50% is the value I want the formula to return if there is not an exact match within the VLOOKUP Table. I think that you've definitely put me on the right track and ISNA is I'm sure the right function. I think the problem revolves around the nesting of the IF Function. To recap the formula needs to check the value i.e. the post code in the VLOOKUP table and if it doesn't make the correct match, it needs to return a percentage value e.g. 0.50% value, but where it it does make an exact match then look at the VLOOKUP table again and return the corresponding percentage value. I'll be so relieved when and if I find a solution; otherwise it means me trawling through a spreadsheet with close to 4,000 rows and manually putting in the percentages. Thanks David -- David "Alan" wrote: You're welcome, Regards, Alan. "David" wrote in message ... Alan, Thank you so much for your help. Your suggested formula was almost right for what I wanted. Rather than use FALSE I needed to substitute this for TRUE. If I hadn't received your input I would have been here until next year! Thanks once again as this is going to save me considerable time on a project I'm working on. -- David "Alan" wrote: Try =IF(ISNA(VLOOKUP(D1,A1:B100,2,FALSE)),"Your different value",VLOOKUP(D1,A1:B100,2,FALSE)) Regards, Alan. "David" wrote in message ... I've been playing around with an IF Function and nesting a VLOOKUP without any luck. I've got a table which has a list of post codes with corresponding values against each. In a separate worksheet I have a list of post codes and where there are post codes the VLOOKUP function works and finds the value; however there are circumstances where the post codes are different and I rather than produce a FALSE statement and put N/A in the column I want it to add a different numerical value. I do not know all the post codes so I cannot add these to the VLOOKUP table. I need the VLOOKUP table to record the exact values that correspond to the post codes. HELP Please -- David |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP - WISH TO SUBSTITUTE FALSE FOR A VALUE
Alan,
Please ignore my last e-mail - I had made a mistake with copying the formula. Your initial formula was in fact right from the start. Apologies for any inconvenience. -- David "Alan" wrote: You're welcome, Regards, Alan. "David" wrote in message ... Alan, Thank you so much for your help. Your suggested formula was almost right for what I wanted. Rather than use FALSE I needed to substitute this for TRUE. If I hadn't received your input I would have been here until next year! Thanks once again as this is going to save me considerable time on a project I'm working on. -- David "Alan" wrote: Try =IF(ISNA(VLOOKUP(D1,A1:B100,2,FALSE)),"Your different value",VLOOKUP(D1,A1:B100,2,FALSE)) Regards, Alan. "David" wrote in message ... I've been playing around with an IF Function and nesting a VLOOKUP without any luck. I've got a table which has a list of post codes with corresponding values against each. In a separate worksheet I have a list of post codes and where there are post codes the VLOOKUP function works and finds the value; however there are circumstances where the post codes are different and I rather than produce a FALSE statement and put N/A in the column I want it to add a different numerical value. I do not know all the post codes so I cannot add these to the VLOOKUP table. I need the VLOOKUP table to record the exact values that correspond to the post codes. HELP Please -- David |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP - WISH TO SUBSTITUTE FALSE FOR A VALUE
Hi David,
Glad you have a resolution, no inconvenience on my part, I've only just looked here today as I've been away for a day or two. Please accept my apologies for not answering your previous post, Regards, Alan. "David" wrote in message ... Alan, Please ignore my last e-mail - I had made a mistake with copying the formula. Your initial formula was in fact right from the start. Apologies for any inconvenience. -- David "Alan" wrote: You're welcome, Regards, Alan. "David" wrote in message ... Alan, Thank you so much for your help. Your suggested formula was almost right for what I wanted. Rather than use FALSE I needed to substitute this for TRUE. If I hadn't received your input I would have been here until next year! Thanks once again as this is going to save me considerable time on a project I'm working on. -- David "Alan" wrote: Try =IF(ISNA(VLOOKUP(D1,A1:B100,2,FALSE)),"Your different value",VLOOKUP(D1,A1:B100,2,FALSE)) Regards, Alan. "David" wrote in message ... I've been playing around with an IF Function and nesting a VLOOKUP without any luck. I've got a table which has a list of post codes with corresponding values against each. In a separate worksheet I have a list of post codes and where there are post codes the VLOOKUP function works and finds the value; however there are circumstances where the post codes are different and I rather than produce a FALSE statement and put N/A in the column I want it to add a different numerical value. I do not know all the post codes so I cannot add these to the VLOOKUP table. I need the VLOOKUP table to record the exact values that correspond to the post codes. HELP Please -- David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop renaming or moving sheet tabs | Excel Discussion (Misc queries) | |||
Help in VBA code! | Excel Discussion (Misc queries) | |||
Inconsistent Macro Behavior | Excel Discussion (Misc queries) | |||
Macros in excel 2000 | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions |