IS ERROR - VLOOKUP Combination

[Tom]

Please see the 2 functions below... I need some help with
combining them in a way that I can
- utilize the VLOOKUP feature
- have the ability to FIND (wildcard) some data within a
cell.


=IF(ISERROR(VLOOKUP(A4,B3:C6,2,FALSE)),"not
found",VLOOKUP >(A4,B3:C6,2))

=IF(MIN(ISERROR(FIND(A1:A30,B1))+0)>0,"Wrong","Ok")


Does anyone know how to do this?


Thanks!!!
Tom

 

[Ken Wright]

Why not give us an idea of what kind of data you are trying to find, and what kind of data it is
to be found in. The following example may be of help, but post back if I'm way off the mark here.
It will take the data in A4 and then look up any value in your table that starts with it:-

=IF(ISERROR(VLOOKUP(A4&"*",B3:C6,2,FALSE)),"not found",VLOOKUP(A4&"*",B3:C6,2,FALSE))

Couldn't see where the MIN bit came into your question though.

 

[Suresh Nair]

Tom
I think u have raised ur question of wild card and i did
share my suggestion. Thinking of giving u further room to
be flexible here`s some more which u have to use carefully.
=IF(ISERROR(VLOOKUP(LEFT(A4&"*",2)
&"*",B3:C6,2,FALSE)),"not found",VLOOKUP(LEFT(A4&"*",2)
&"*",B3:C6,2,0))

HTH
Suresh

 

[Tom]

Suresh:

Thanks for your reply... This works fantastically!!!

Why did you suggest to use it "carefully? Are there any
potential cases where it might pick up incorrect values?

Thanks again!!!!

Tom

 

[Tom]

Hi Ken:

I appreciate your feedback.

Yes, I should have provided more info as to what the
problem is.

I installed a feature into Excel that compares worksheets.
I have thousands of records of which I need to track a
changes for historical purposes.

If, out of those thousands of records, some value has
changed the data validation function creates another
sheet which lists the cell address and the old/new values
in the compared sheets.

I may have hundreds of "erros" were the change took place
in the same column. Hence, the cell address listed are
$A$1, $A$5, $A$64... and so on.

I now want to add another column into the comparison (for
reporting purposes) which will lists the column header
(field name) for all "errors" beginning with $A$.

For that I needed to point to an array listing all field
names. However, because of potentially hundreds of
errors within the same field I needed to use the wildcard
which replaces the number (row) of the cell address.

*********

Okay, that's pretty much covering the problem... I looked
at your function and inserter it into my worksheet.

Unfortunately, it does not pick up the values listed in
the array.

I have tried the posting/function Suresh Nair (2nd
posting to this question)... it picks up the values
properly. [Suresh had the benefit of knowing the problem
from another posting -- I am not trying to highlight
abilities here... just references for other users who
have similar problems].

In respect to the MIN... I had only found 2 function
which seemed to makes sense by themselves. I simply
posted them into the question. 'Don't know what the
purposes of the MIN really is.


Thanks again for helping me out!!!

Tom

-----Original Message-----
Why not give us an idea of what kind of data you are

trying to find, and what kind of data it is

to be found in. The following example may be of help,

but post back if I'm way off the mark here.

It will take the data in A4 and then look up any value

in your table that starts with it:-

 

[Suresh Nair]

Tom
Pleasure Tom, carefully - just because i wanted u to test
the formula as i am not sure of your logic and use.
Regds
Suresh

 

[Ken Wright]

No problem, as long as you got sorted one way or another, that is what counts.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

Hi Ken:

I appreciate your feedback.

Yes, I should have provided more info as to what the
problem is.

I installed a feature into Excel that compares worksheets.
I have thousands of records of which I need to track a
changes for historical purposes.

If, out of those thousands of records, some value has
changed the data validation function creates another
sheet which lists the cell address and the old/new values
in the compared sheets.

I may have hundreds of "erros" were the change took place
in the same column. Hence, the cell address listed are
$A$1, $A$5, $A$64... and so on.

I now want to add another column into the comparison (for
reporting purposes) which will lists the column header
(field name) for all "errors" beginning with $A$.

For that I needed to point to an array listing all field
names. However, because of potentially hundreds of
errors within the same field I needed to use the wildcard
which replaces the number (row) of the cell address.

*********

Okay, that's pretty much covering the problem... I looked
at your function and inserter it into my worksheet.

Unfortunately, it does not pick up the values listed in
the array.

I have tried the posting/function Suresh Nair (2nd
posting to this question)... it picks up the values
properly. [Suresh had the benefit of knowing the problem
from another posting -- I am not trying to highlight
abilities here... just references for other users who
have similar problems].

In respect to the MIN... I had only found 2 function
which seemed to makes sense by themselves. I simply
posted them into the question. 'Don't know what the
purposes of the MIN really is.


Thanks again for helping me out!!!

Tom

-----Original Message-----
Why not give us an idea of what kind of data you are

trying to find, and what kind of data it is

to be found in. The following example may be of help,

but post back if I'm way off the mark here.

It will take the data in A4 and then look up any value

in your table that starts with it:-

=IF(ISERROR(VLOOKUP(A4&"*",B3:C6,2,FALSE)),"not found",VLOOKUP(A4&"*",B3:C6,2,FALSE))

Couldn't see where the MIN bit came into your question though.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

--------------------------------------------------------- -------------------
Attitude - A little thing that makes a BIG difference
--------------------------------------------------------- -------------------






.

 

[Tom]

Suresh,

forgot to mention... this is what the modified function
currently looks like:

=IF(ISERROR(VLOOKUP(LEFT(A2 &"*",2) &"*",FieldNameArray!
$A$1:$B$31,2,FALSE)),"not found",VLOOKUP(LEFT(A2&"*",2)
&"*",FieldNameArray!$A$1:$B$31,2,0))

Thanks again,
Tom

 

[Tom]

Thanks, Suresh.

Tom

-----Original Message-----
Tom

In the function "left" within the formula states ,2 which
means 2 characters from left just increase or decrease the
.