| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Comparing Two Sheets | Excel Worksheet Functions | |||
| Comparing multiple columns in two sheets | Excel Worksheet Functions | |||
| comparing 2 sheets | Excel Worksheet Functions | |||
| Comparing columns iin 2 sheets to generate one | Excel Worksheet Functions | |||
| comparing 2 similar columns on seperate work sheets in 1 workbook | Excel Discussion (Misc queries) | |||
Linear Mode
