Comparing columns in sheets
 

Thanks for reading this post.

In Sheet 1 column B I have a list of numbers(eg. Staff No) and in column F
there are some values (eg. Incentive) against each number. Sheet 2 column C
contains a list of numbers which includes many of the numbers from the
previous sheet (ie. sheet 1 Column B) but not in the same sequence. Column H
has the values.

I want to compare sheet 1 column B with sheet 2 column C and where a match
is found to display the corresponding value from sheet 1 column F in sheet 2
column J.

How can I achieve this?

Comparing columns in sheets
 

Hi,,

Try this in Sheet 2 j1 and drag down to the length of column B.

=IF(ISNA(VLOOKUP(C1,Sheet1!B$1:F$6,5,FALSE)),"Not
found",VLOOKUP(C1,Sheet1!B$1:F$6,5,FALSE))

Mike


"jpreman" wrote:

Thanks for reading this post.

In Sheet 1 column B I have a list of numbers(eg. Staff No) and in column F
there are some values (eg. Incentive) against each number. Sheet 2 column C
contains a list of numbers which includes many of the numbers from the
previous sheet (ie. sheet 1 Column B) but not in the same sequence. Column H
has the values.

I want to compare sheet 1 column B with sheet 2 column C and where a match
is found to display the corresponding value from sheet 1 column F in sheet 2
column J.

How can I achieve this?

Comparing columns in sheets
 

Hi Mike,

That's really fast.

Thanks a lot

Mike, can you kindly explain your formula in brief.


"Mike H" wrote:

Hi,,

Try this in Sheet 2 j1 and drag down to the length of column B.

=IF(ISNA(VLOOKUP(C1,Sheet1!B$1:F$6,5,FALSE)),"Not
found",VLOOKUP(C1,Sheet1!B$1:F$6,5,FALSE))

Mike


"jpreman" wrote:

Thanks for reading this post.

In Sheet 1 column B I have a list of numbers(eg. Staff No) and in column F
there are some values (eg. Incentive) against each number. Sheet 2 column C
contains a list of numbers which includes many of the numbers from the
previous sheet (ie. sheet 1 Column B) but not in the same sequence. Column H
has the values.

I want to compare sheet 1 column B with sheet 2 column C and where a match
is found to display the corresponding value from sheet 1 column F in sheet 2
column J.

How can I achieve this?

Comparing columns in sheets
 

Hi jpreman:

This is not the answer. I hope it could be a start. Go to Excel help, to the
Answer Wizard tab. Type "IF Worksheet Function" (without the quotes). That, I
think, will do what you want. It will take an involved formula.

Also, the Exact and the OR function will be there and may help. You can use
more than one of these together, in the same formula.

If your experience is like mine has been, sometimes I find some or part of
my answer but I learn a ton from the search.

Good hunting

--
19A14D1N


"jpreman" wrote:

Thanks for reading this post.

In Sheet 1 column B I have a list of numbers(eg. Staff No) and in column F
there are some values (eg. Incentive) against each number. Sheet 2 column C
contains a list of numbers which includes many of the numbers from the
previous sheet (ie. sheet 1 Column B) but not in the same sequence. Column H
has the values.

I want to compare sheet 1 column B with sheet 2 column C and where a match
is found to display the corresponding value from sheet 1 column F in sheet 2
column J.

How can I achieve this?

Comparing columns in sheets
 

Hi TIG55,

Thanks.

I will try your suggestion.

Your are absolutely right. I too learn a lot in this manner



"TIG55" wrote:

Hi jpreman:

This is not the answer. I hope it could be a start. Go to Excel help, to the
Answer Wizard tab. Type "IF Worksheet Function" (without the quotes). That, I
think, will do what you want. It will take an involved formula.

Also, the Exact and the OR function will be there and may help. You can use
more than one of these together, in the same formula.

If your experience is like mine has been, sometimes I find some or part of
my answer but I learn a ton from the search.

Good hunting

--
19A14D1N


"jpreman" wrote:

Thanks for reading this post.

In Sheet 1 column B I have a list of numbers(eg. Staff No) and in column F
there are some values (eg. Incentive) against each number. Sheet 2 column C
contains a list of numbers which includes many of the numbers from the
previous sheet (ie. sheet 1 Column B) but not in the same sequence. Column H
has the values.

I want to compare sheet 1 column B with sheet 2 column C and where a match
is found to display the corresponding value from sheet 1 column F in sheet 2
column J.

How can I achieve this?

Comparing columns in sheets
 

Hi,

Im glad it solved you problem. How does it work?

VLOOKUP(C1,Sheet1!B$1:F$6,5,FALSE)
The looks for the value held in c1 in b1 - b6 of sheet 1. If it finds it
then the corresponding value colum 5 of the array is returned (col F). False
tells it to return exact matches only.

Mike


"jpreman" wrote:

Hi Mike,

That's really fast.

Thanks a lot

Mike, can you kindly explain your formula in brief.


"Mike H" wrote:

Hi,,

Try this in Sheet 2 j1 and drag down to the length of column B.

=IF(ISNA(VLOOKUP(C1,Sheet1!B$1:F$6,5,FALSE)),"Not
found",VLOOKUP(C1,Sheet1!B$1:F$6,5,FALSE))

Mike


"jpreman" wrote:

Thanks for reading this post.

In Sheet 1 column B I have a list of numbers(eg. Staff No) and in column F
there are some values (eg. Incentive) against each number. Sheet 2 column C
contains a list of numbers which includes many of the numbers from the
previous sheet (ie. sheet 1 Column B) but not in the same sequence. Column H
has the values.

I want to compare sheet 1 column B with sheet 2 column C and where a match
is found to display the corresponding value from sheet 1 column F in sheet 2
column J.

How can I achieve this?

Comparing columns in sheets
 

Hi Mike,

It works fine.

Thanks again for the explanation.


"Mike H" wrote:

Hi,

Im glad it solved you problem. How does it work?

VLOOKUP(C1,Sheet1!B$1:F$6,5,FALSE)
The looks for the value held in c1 in b1 - b6 of sheet 1. If it finds it
then the corresponding value colum 5 of the array is returned (col F). False
tells it to return exact matches only.

Mike


"jpreman" wrote:

Hi Mike,

That's really fast.

Thanks a lot

Mike, can you kindly explain your formula in brief.


"Mike H" wrote:

Hi,,

Try this in Sheet 2 j1 and drag down to the length of column B.

=IF(ISNA(VLOOKUP(C1,Sheet1!B$1:F$6,5,FALSE)),"Not
found",VLOOKUP(C1,Sheet1!B$1:F$6,5,FALSE))

Mike


"jpreman" wrote:

Thanks for reading this post.

In Sheet 1 column B I have a list of numbers(eg. Staff No) and in column F
there are some values (eg. Incentive) against each number. Sheet 2 column C
contains a list of numbers which includes many of the numbers from the
previous sheet (ie. sheet 1 Column B) but not in the same sequence. Column H
has the values.

I want to compare sheet 1 column B with sheet 2 column C and where a match
is found to display the corresponding value from sheet 1 column F in sheet 2
column J.

How can I achieve this?