VLOOKUP - WISH TO SUBSTITUTE FALSE FOR A VALUE

[Guest]

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

 

[Alan]

Try
=IF(ISNA(VLOOKUP(D1,A1:B100,2,FALSE)),"Your different
value",VLOOKUP(D1,A1:B100,2,FALSE))
Regards,
Alan.

 

[Guest]

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.

 

[Alan]

You're welcome,
Regards,
Alan.

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.

 

[Guest]

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%,VLOOKUP(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

 

[Guest]

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.

 

[Alan]

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.