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

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

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

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

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

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

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