using VLOOKUP across two separate worksheets

Hi,

Here is a description of my problem:


I have a project that contains two very large worksheets that are possibly
different sizes (depending on results of a program. approximately 3000-8000
lines). The first sheet has a value in column H, and I need to verify that
the variable in column 8 of the first sheet exists in column A of the second
sheet. If it exists, I want to populate a 3rd sheet with that value. If it
does not exist, I want to populate the 3rd sheet with a blank in that value's
spot. So, the 3rd sheet will be the same size as the 1st sheet (it will
either have the value that was found or a blank spot).


thanks for your help!

Hi,
Please try this:

In cell A1 on the third worksheet enter:

=IF(ISNA(VLOOKUP(Sheet1!H1,Sheet2!$A$1:$A$9000,1,FALSE)),"",VLOOKUP(Sheet1!H1,Sheet2!$A$1:$A$9000,1,FALSE))

--
Ken Hudson


"njuneardave" wrote:

> Hi,
>
> Here is a description of my problem:
>
>
> I have a project that contains two very large worksheets that are possibly
> different sizes (depending on results of a program. approximately 3000-8000
> lines). The first sheet has a value in column H, and I need to verify that
> the variable in column 8 of the first sheet exists in column A of the second
> sheet. If it exists, I want to populate a 3rd sheet with that value. If it
> does not exist, I want to populate the 3rd sheet with a blank in that value's
> spot. So, the 3rd sheet will be the same size as the 1st sheet (it will
> either have the value that was found or a blank spot).
>
>
> thanks for your help!

Ken,

Logically, that works for me. It looks correct. However, when I put it
into my "output sheet" in column A1....nothing happens. It is pasted in
there, but the cell still remains blank. Any advice?

Thanks for the help!

"Ken Hudson" wrote:

> Hi,
> Please try this:
>
> In cell A1 on the third worksheet enter:
>
> =IF(ISNA(VLOOKUP(Sheet1!H1,Sheet2!$A$1:$A$9000,1,FALSE)),"",VLOOKUP(Sheet1!H1,Sheet2!$A$1:$A$9000,1,FALSE))
>
> --
> Ken Hudson
>
>
> "njuneardave" wrote:
>
> > Hi,
> >
> > Here is a description of my problem:
> >
> >
> > I have a project that contains two very large worksheets that are possibly
> > different sizes (depending on results of a program. approximately 3000-8000
> > lines). The first sheet has a value in column H, and I need to verify that
> > the variable in column 8 of the first sheet exists in column A of the second
> > sheet. If it exists, I want to populate a 3rd sheet with that value. If it
> > does not exist, I want to populate the 3rd sheet with a blank in that value's
> > spot. So, the 3rd sheet will be the same size as the 1st sheet (it will
> > either have the value that was found or a blank spot).
> >
> >
> > thanks for your help!

Ken,

disregard that other post. I was making a stupid mistake (as in, the first
value of that table didn't have a corresponding value in sheet2). Yes, your
solution worked wonderfully. Thank you for that help!


Now, a followup question:

Say I did that procedure to populate a column of sheet3 (output sheet).
Then I had another column populated thru a similar VLOOKUP function (say,
column2). Now, if both columns returned a valid value (instead of a
blank..or NaN), I would want to populate the output sheet completely with all
of the values of Sheet1 (the values are located in Columns A - L) for the
corresponding valid values. However, if one of the two columns returned a
NaN/blank, I would leave the rest of the columns blank.

Basically, I want to duplicate all of the info from Sheet1 if the values
from the 2 columns of Sheet1 are corresponding in the Sheet2, but if the
value was invalid in either column1 or column2, I want to leave the rest
blank. Suppose I want this output in column3

"njuneardave" wrote:

> Hi,
>
> Here is a description of my problem:
>
>
> I have a project that contains two very large worksheets that are possibly
> different sizes (depending on results of a program. approximately 3000-8000
> lines). The first sheet has a value in column H, and I need to verify that
> the variable in column 8 of the first sheet exists in column A of the second
> sheet. If it exists, I want to populate a 3rd sheet with that value. If it
> does not exist, I want to populate the 3rd sheet with a blank in that value's
> spot. So, the 3rd sheet will be the same size as the 1st sheet (it will
> either have the value that was found or a blank spot).
>
>
> thanks for your help!

Also, in addition to the code that you wrote:

=IF(ISNA(VLOOKUP(Data!H1,Types!$A$1:$A$9000,1,FALSE)),"",VLOOKUP(Data!H1,Types!$A$1:$A$9000,1,FALSE))

What if there are values that I know are in Sheet 1 but not in Sheet 2 that
I want to exclude from the search. suppose, for example, Sheet 1 had like
"Balloons" in it 2000 times, but Sheet2 never had balloons (and i know this),
so I want to exclude balloons from my search. How would i do that? There
are three values that I want to leave out from the search: balloons,
confetti, and streamers. How do I exclude those three from that original
search code that you posted?

Thanks!

"njuneardave" wrote:

> Hi,
>
> Here is a description of my problem:
>
>
> I have a project that contains two very large worksheets that are possibly
> different sizes (depending on results of a program. approximately 3000-8000
> lines). The first sheet has a value in column H, and I need to verify that
> the variable in column 8 of the first sheet exists in column A of the second
> sheet. If it exists, I want to populate a 3rd sheet with that value. If it
> does not exist, I want to populate the 3rd sheet with a blank in that value's
> spot. So, the 3rd sheet will be the same size as the 1st sheet (it will
> either have the value that was found or a blank spot).
>
>
> thanks for your help!

Hi,
When we get to this level of complexity, I usually resort to a macro solution.

1. You want to exclude from the output on Sheet3 all rows of data on Sheet1
that
have "balloons", "confetti", or "streamers" in column A? Could these words
be in column B also? If so, do we test for that and exclude that row based on
column B?

2. You want to compare column A on Sheet1 to column A on Sheet2 and column B
on Sheet1 to column B on sheet2. If they match, you want to copy columns A-L
from Sheet1 to Sheet3?

Is this the correct logic?
--
Ken Hudson


"njuneardave" wrote:

> Also, in addition to the code that you wrote:
>
> =IF(ISNA(VLOOKUP(Data!H1,Types!$A$1:$A$9000,1,FALSE)),"",VLOOKUP(Data!H1,Types!$A$1:$A$9000,1,FALSE))
>
> What if there are values that I know are in Sheet 1 but not in Sheet 2 that
> I want to exclude from the search. suppose, for example, Sheet 1 had like
> "Balloons" in it 2000 times, but Sheet2 never had balloons (and i know this),
> so I want to exclude balloons from my search. How would i do that? There
> are three values that I want to leave out from the search: balloons,
> confetti, and streamers. How do I exclude those three from that original
> search code that you posted?
>
> Thanks!
>
> "njuneardave" wrote:
>
> > Hi,
> >
> > Here is a description of my problem:
> >
> >
> > I have a project that contains two very large worksheets that are possibly
> > different sizes (depending on results of a program. approximately 3000-8000
> > lines). The first sheet has a value in column H, and I need to verify that
> > the variable in column 8 of the first sheet exists in column A of the second
> > sheet. If it exists, I want to populate a 3rd sheet with that value. If it
> > does not exist, I want to populate the 3rd sheet with a blank in that value's
> > spot. So, the 3rd sheet will be the same size as the 1st sheet (it will
> > either have the value that was found or a blank spot).
> >
> >
> > thanks for your help!