Comparing columns in sheets

[Guest]

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?

 

[Guest]

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

 

[Guest]

Hi Mike,

That's really fast.

Thanks a lot

Mike, can you kindly explain your formula in brief.

 

[Guest]

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

 

[Guest]

Hi TIG55,

Thanks.

I will try your suggestion.

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

 

[Guest]

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

 

[Guest]

Hi Mike,

It works fine.

Thanks again for the explanation.