=?Utf-8?Q?If_/_Vlookup_Formula_Help_=E2=80=A6!!?=

[Monk]

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))," ",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,
Monk

[Kevin B]

Have you tried changing your optional False statement to true so the VLOOKUP
only looks for an exact match?
--
Kevin Backmann


"Monk" wrote:

> 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))," ",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,
> Monk
>

[Niek Otten]

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

--
Kind regards,

Niek Otten

"Kevin B" <[email protected]> wrote in message
news:[email protected]...
> Have you tried changing your optional False statement to true so the
> VLOOKUP
> only looks for an exact match?
> --
> Kevin Backmann
>
>
> "Monk" wrote:
>
>> 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))," ",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,
>> Monk
>>

[L. Howard Kittle]

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

"Monk" <[email protected]> wrote in message
news:[email protected]...
> 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))," ",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,
> Monk
>