IS ERROR - VLOOKUP Combination

  • Thread starter Thread starter Tom
  • Start date Start date
T

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
 
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.
 
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
 
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
 
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
Click to expand...
trying to find, and what kind of data it is
to be found in. The following example may be of help,
Click to expand...
but post back if I'm way off the mark here.
It will take the data in A4 and then look up any value
Click to expand...
in your table that starts with it:-
 
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
 
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
----------------------------------------------------------------------------



Tom said:
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
Click to expand...
trying to find, and what kind of data it is
to be found in. The following example may be of help,
Click to expand...
but post back if I'm way off the mark here.
It will take the data in A4 and then look up any value
Click to expand...
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
--------------------------------------------------------- -------------------






.
Click to expand...
Click to expand...
 
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
 
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
.
Click to expand...
 
Back
Top