Thank you,
Your corrected formula works well with one lasting problem. If I remove
the data that was used for the search all the results of the search are wiped
out. I tried to remove the data to use new data for new search. Can
anything be done to correct this situation???
"Ragdyer" wrote:
I can see that I left out a parenthesis.
I tested this against your scenario in your original post, and this *does*
work:
=IF(ISNA(MATCH(A1,Sheet2!$A$1:$A$10,0)),"",VLOOKUP (A1,Sheet2!$A$1:$D$10,4,0)
)
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"George A. Yorks" .(donotspam) wrote in message
...
I tried using the formula below<from =IF to 4,0) and got message too many
arguments. Bottom line it does not change the #N/A to ) which will allow
the
column to be added. Any other thoughts.
Thanks much
"RagDyer" wrote:
First question:
Replace error message with a null ( "" ), which can then be added:
=IF(ISNA(MATCH(A1,'Sheet2'!$A$1:$A$10,0)),"",VLOOK UP(A1,
'Sheet2'!$A$1:$D$10, 4, 0)
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
"George A. Yorks" .(donotspam) wrote in message
...
I keep saying thanks, your help is outstanding. I would like to ask two
additional questions. ie: When I enter my formula into the cells of
column
A
in a number of cells it returns a value in a few it returns#N/A. There
is
no data in those cases but with the #n?A when I try to add the columns
will
not do so as it cant enter a non digit. I'm using 0 for the last number
in
my formula. How best to get around this.
Also I've copied a table from USA internet. In pasting to worksheet all
the
cells are obliterated and nothing is recognized by excel. The data is
however recognized in (pardon me) lotus. Is there anyway of having my
vlookup formula search 123 in place of a sheet in excel. =vlookup(a1,
sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to someone
"Earl Kiosterud" wrote:
George,
The 4 is the third parameter of the VLOOKUP function, and tells it to
retrieve the cell in the 4th column of the range being looked up in.
Did
I
say being looked up in? Oh, well. :)
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------
"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. I know I'll fully understand the formula
structure
soon. One question, at end of formula ,4,0 what in fact does the 4
make
reference to?
"Ragdyer" wrote:
Try this:
=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)
--
HTH,
RD
-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!
-------------------------------------------------------------------------
--
"George A. Yorks" .(donotspam) wrote in message
...
Thanks for the help. One further question.
In the formul In sheet 2 $A$1:$b$10makes reference to two
adjacent
columns.
If there is a column between so there is data in a and c and none
in
b
the
result return err,the formula in this cell referes to cells that
are
currently emply. How to get around this. ie" if sheet two has
data
in
column
a and column d.
thanks
George Yorks
"Earl Kiosterud" wrote:
George,
In B1 of sheet 1:
=VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE)
Copy down with fill handle to B10.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------
"George A. Yorks" .(donotspam) wrote in
message
...
Trying to create a formula to do the following:
Sheet 1 column A a list of personal names a1-a10
Sheet 2 has list of names a1-a10 and list of dollar amounts
colums
d1-d10
want to search sheet one and if any name from sheet 2 found
on
sheet 1
than
the corresponding dollar amount is entered.
Any help appreciated.
--
George
|