software

Hi there..,
I’m experiencing a problem with a formula I am attempting to compile
(nothing new there)

Basically I require a formula to lookup some text within a specified cell
(we shall say ‘A1’) and return the given result that identically matches the
text throughout a large table (we shall say ‘B1:G2500’). The formula im using
…..

=IF(ISNA(VLOOKUP($A$1,$B$1:$G$2500,3,FALSE)),quot; quot;,VLOOKUP($A$1,$B$1:
$G$2500,3,FALSE))

…. is returning all the correct results by searching column B for the
specified text and in the above case is returning the result within the same
row on column D (3).
However the problem im finding is.., if column B doesn’t contain the
specified text it’s returning a duplicate result of the nearest cell that
does contain the text (this is bad) and if I lose the appropriate ‘$’ symbols
within my formula, it will happily return a completely blank row (also bad).

Is it possible to amend this formula (or provide a new one) which would
return the above results without duplicating or leaving a blank cell.
Basically it would just move on to the next cell which contains the specified
text (similar to applying a filter, which I wish not use if at all possible).

Any thoughts.. many thanks,
MonkHave you tried changing your optional False statement to true so the VLOOKUP
only looks for an exact match?
--
Kevin Backmannquot;Monkquot; wrote:

gt; Hi there..,
gt; I’m experiencing a problem with a formula I am attempting to compile
gt; (nothing new there)
gt;
gt; Basically I require a formula to lookup some text within a specified cell
gt; (we shall say ‘A1’) and return the given result that identically matches the
gt; text throughout a large table (we shall say ‘B1:G2500’). The formula im using
gt; …..
gt;
gt; =IF(ISNA(VLOOKUP($A$1,$B$1:$G$2500,3,FALSE)),quot; quot;,VLOOKUP($A$1,$B$1:
gt; $G$2500,3,FALSE))
gt;
gt; …. is returning all the correct results by searching column B for the
gt; specified text and in the above case is returning the result within the same
gt; row on column D (3).
gt; However the problem im finding is.., if column B doesn’t contain the
gt; specified text it’s returning a duplicate result of the nearest cell that
gt; does contain the text (this is bad) and if I lose the appropriate ‘$’ symbols
gt; within my formula, it will happily return a completely blank row (also bad).
gt;
gt; Is it possible to amend this formula (or provide a new one) which would
gt; return the above results without duplicating or leaving a blank cell.
gt; Basically it would just move on to the next cell which contains the specified
gt; text (similar to applying a filter, which I wish not use if at all possible).
gt;
gt; Any thoughts.. many thanks,
gt; Monk
gt;

It's the other way around, Kevin, that's what puzzles me

--
Kind regards,

Niek Otten

quot;Kevin Bquot; gt; wrote in message
...
gt; Have you tried changing your optional False statement to true so the
gt; VLOOKUP
gt; only looks for an exact match?
gt; --
gt; Kevin Backmann
gt;
gt;
gt; quot;Monkquot; wrote:
gt;
gt;gt; Hi there..,
gt;gt; I'm experiencing a problem with a formula I am attempting to compile
gt;gt; (nothing new there)
gt;gt;
gt;gt; Basically I require a formula to lookup some text within a specified cell
gt;gt; (we shall say 'A1') and return the given result that identically matches
gt;gt; the
gt;gt; text throughout a large table (we shall say 'B1:G2500'). The formula im
gt;gt; using
gt;gt; ...
gt;gt;
gt;gt; =IF(ISNA(VLOOKUP($A$1,$B$1:$G$2500,3,FALSE)),quot; quot;,VLOOKUP($A$1,$B$1:
gt;gt; $G$2500,3,FALSE))
gt;gt;
gt;gt; .. is returning all the correct results by searching column B for the
gt;gt; specified text and in the above case is returning the result within the
gt;gt; same
gt;gt; row on column D (3).
gt;gt; However the problem im finding is.., if column B doesn't contain the
gt;gt; specified text it's returning a duplicate result of the nearest cell that
gt;gt; does contain the text (this is bad) and if I lose the appropriate '$'
gt;gt; symbols
gt;gt; within my formula, it will happily return a completely blank row (also
gt;gt; bad).
gt;gt;
gt;gt; Is it possible to amend this formula (or provide a new one) which would
gt;gt; return the above results without duplicating or leaving a blank cell.
gt;gt; Basically it would just move on to the next cell which contains the
gt;gt; specified
gt;gt; text (similar to applying a filter, which I wish not use if at all
gt;gt; possible).
gt;gt;
gt;gt; Any thoughts.. many thanks,
gt;gt; Monk
gt;gt;
Hi Monk,

I'm with Niek, looks good to me. I would look at a sample workbook if you
want to send me one.

Regards,
Howard

quot;Monkquot; gt; wrote in message
...
gt; Hi there..,
gt; I'm experiencing a problem with a formula I am attempting to compile
gt; (nothing new there)
gt;
gt; Basically I require a formula to lookup some text within a specified cell
gt; (we shall say 'A1') and return the given result that identically matches
gt; the
gt; text throughout a large table (we shall say 'B1:G2500'). The formula im
gt; using
gt; ...
gt;
gt; =IF(ISNA(VLOOKUP($A$1,$B$1:$G$2500,3,FALSE)),quot; quot;,VLOOKUP($A$1,$B$1:
gt; $G$2500,3,FALSE))
gt;
gt; .. is returning all the correct results by searching column B for the
gt; specified text and in the above case is returning the result within the
gt; same
gt; row on column D (3).
gt; However the problem im finding is.., if column B doesn't contain the
gt; specified text it's returning a duplicate result of the nearest cell that
gt; does contain the text (this is bad) and if I lose the appropriate '$'
gt; symbols
gt; within my formula, it will happily return a completely blank row (also
gt; bad).
gt;
gt; Is it possible to amend this formula (or provide a new one) which would
gt; return the above results without duplicating or leaving a blank cell.
gt; Basically it would just move on to the next cell which contains the
gt; specified
gt; text (similar to applying a filter, which I wish not use if at all
gt; possible).
gt;
gt; Any thoughts.. many thanks,
gt; Monk
gt;