Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"lookup" formulas
Please could someone help me with look up tables.
I have 2 different worksheets within one spreadsheet. The one worksheet contains an account number eg 00 00000 000 with an account name in the column adjacent to the account number. The other shows just the account number and payment amount. Is there any formula i could use which looks up the account name from the one worksheet and adds the account name next to the number in another worksheet? I have thousands of entries to complete so any help at all would br brilliant. Thanks -- RMP |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
"lookup" formulas
Assume your table with the account code and name is on Sheet1,
occupying A1 to B1000. Assume the other sheet is called Sheet2, and has account code in column A and payment amount in column B. First of all, insert a new column B in Sheet2, and enter this formula in B2, assuming your data starts in A2 - maybe with a header row: =VLOOKUP(A2,Sheet1!A$1:B$1000,2,0) Adjust the ranges to suit your table. Copy this formula down column B for as many items as you have in column A. Hope this helps. Pete On Feb 24, 9:29 pm, RMP wrote: Please could someone help me with look up tables. I have 2 different worksheets within one spreadsheet. The one worksheet contains an account number eg 00 00000 000 with an account name in the column adjacent to the account number. The other shows just the account number and payment amount. Is there any formula i could use which looks up the account name from the one worksheet and adds the account name next to the number in another worksheet? I have thousands of entries to complete so any help at all would br brilliant. Thanks -- RMP |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
"lookup" formulas
Appreciate your help.
I have set the spreadsheet and formulas up exactly as you have described below but i am getting the following erroe reference in cell B2 worksheet 2- #N/A. Are you able to describe what each part of the formula refers to specifically to help my understanding of it? Please could you break it down? Thanks -- RMP "Pete_UK" wrote: Assume your table with the account code and name is on Sheet1, occupying A1 to B1000. Assume the other sheet is called Sheet2, and has account code in column A and payment amount in column B. First of all, insert a new column B in Sheet2, and enter this formula in B2, assuming your data starts in A2 - maybe with a header row: =VLOOKUP(A2,Sheet1!A$1:B$1000,2,0) Adjust the ranges to suit your table. Copy this formula down column B for as many items as you have in column A. Hope this helps. Pete On Feb 24, 9:29 pm, RMP wrote: Please could someone help me with look up tables. I have 2 different worksheets within one spreadsheet. The one worksheet contains an account number eg 00 00000 000 with an account name in the column adjacent to the account number. The other shows just the account number and payment amount. Is there any formula i could use which looks up the account name from the one worksheet and adds the account name next to the number in another worksheet? I have thousands of entries to complete so any help at all would br brilliant. Thanks -- RMP |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
"lookup" formulas
I'll try to fill in for Pete_UK for a minute.
The #N/A means that the value you were looking for was not found in the source list. In VLOOKUP() (as explained in Excel Help on the subject) Using the formula Pete gave for reference: the first parameter is what you are looking for (whatever is displayed in A2 on the current sheet) the second parameter is where to look for it, in this case it is going to look for it in column A of a matrix that goes from A1 to B1000 on Sheet1. The third parameter, 2 - says when you find a match for this, return the value in the second column of the lookup array. Finally, the ,0) says that your list in the lookup array, column A, does not have to be sorted in any particular order. I suppose the question at this point is, assuming you put the formula on Sheet2 in cell B2 exactly as Pete_UK gave it to you: What is in cell A2 on Sheet2, and is it definitely a match for an entry in Column A (rows 1 through 1000) on Sheet1 ?? Since you're getting #N/A, I'm assuming either nothing or a typo in A2 on Sheet2. Now, if the value in A2 on Sheet2 should NOT be on Sheet1 in cells A1:A1000, and you want to keep the #N/A from showing up, write it this way: =IF(ISNA(VLOOKUP(A2,Sheet1!A$1:B$1000,2,0)),"",VLO OKUP(A2,Sheet1!A$1:B$1000,2,0))) that says that if when attempting to do the VLOOKUP an #N/A error takes place, just output an empty string ( ,"", in the formula), but if things worked ok, then show me the result. "RMP" wrote: Appreciate your help. I have set the spreadsheet and formulas up exactly as you have described below but i am getting the following erroe reference in cell B2 worksheet 2- #N/A. Are you able to describe what each part of the formula refers to specifically to help my understanding of it? Please could you break it down? Thanks -- RMP "Pete_UK" wrote: Assume your table with the account code and name is on Sheet1, occupying A1 to B1000. Assume the other sheet is called Sheet2, and has account code in column A and payment amount in column B. First of all, insert a new column B in Sheet2, and enter this formula in B2, assuming your data starts in A2 - maybe with a header row: =VLOOKUP(A2,Sheet1!A$1:B$1000,2,0) Adjust the ranges to suit your table. Copy this formula down column B for as many items as you have in column A. Hope this helps. Pete On Feb 24, 9:29 pm, RMP wrote: Please could someone help me with look up tables. I have 2 different worksheets within one spreadsheet. The one worksheet contains an account number eg 00 00000 000 with an account name in the column adjacent to the account number. The other shows just the account number and payment amount. Is there any formula i could use which looks up the account name from the one worksheet and adds the account name next to the number in another worksheet? I have thousands of entries to complete so any help at all would br brilliant. Thanks -- RMP |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
"lookup" formulas
Thank you for your very detailed response. I made a few adjustments to the
formula and found the errors. One error which i do not fully understand is that one of the cells I was evaluating contained a constant? Anyway, I have learnt a lot and have been able to get the formula's to work thanks to yours and Pete_UK's response. Thanks again, very much appreciated!!!! -- RMP "JLatham" wrote: I'll try to fill in for Pete_UK for a minute. The #N/A means that the value you were looking for was not found in the source list. In VLOOKUP() (as explained in Excel Help on the subject) Using the formula Pete gave for reference: the first parameter is what you are looking for (whatever is displayed in A2 on the current sheet) the second parameter is where to look for it, in this case it is going to look for it in column A of a matrix that goes from A1 to B1000 on Sheet1. The third parameter, 2 - says when you find a match for this, return the value in the second column of the lookup array. Finally, the ,0) says that your list in the lookup array, column A, does not have to be sorted in any particular order. I suppose the question at this point is, assuming you put the formula on Sheet2 in cell B2 exactly as Pete_UK gave it to you: What is in cell A2 on Sheet2, and is it definitely a match for an entry in Column A (rows 1 through 1000) on Sheet1 ?? Since you're getting #N/A, I'm assuming either nothing or a typo in A2 on Sheet2. Now, if the value in A2 on Sheet2 should NOT be on Sheet1 in cells A1:A1000, and you want to keep the #N/A from showing up, write it this way: =IF(ISNA(VLOOKUP(A2,Sheet1!A$1:B$1000,2,0)),"",VLO OKUP(A2,Sheet1!A$1:B$1000,2,0))) that says that if when attempting to do the VLOOKUP an #N/A error takes place, just output an empty string ( ,"", in the formula), but if things worked ok, then show me the result. "RMP" wrote: Appreciate your help. I have set the spreadsheet and formulas up exactly as you have described below but i am getting the following erroe reference in cell B2 worksheet 2- #N/A. Are you able to describe what each part of the formula refers to specifically to help my understanding of it? Please could you break it down? Thanks -- RMP "Pete_UK" wrote: Assume your table with the account code and name is on Sheet1, occupying A1 to B1000. Assume the other sheet is called Sheet2, and has account code in column A and payment amount in column B. First of all, insert a new column B in Sheet2, and enter this formula in B2, assuming your data starts in A2 - maybe with a header row: =VLOOKUP(A2,Sheet1!A$1:B$1000,2,0) Adjust the ranges to suit your table. Copy this formula down column B for as many items as you have in column A. Hope this helps. Pete On Feb 24, 9:29 pm, RMP wrote: Please could someone help me with look up tables. I have 2 different worksheets within one spreadsheet. The one worksheet contains an account number eg 00 00000 000 with an account name in the column adjacent to the account number. The other shows just the account number and payment amount. Is there any formula i could use which looks up the account name from the one worksheet and adds the account name next to the number in another worksheet? I have thousands of entries to complete so any help at all would br brilliant. Thanks -- RMP |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
"lookup" formulas
You're welcome - thanks for feeding back.
And thanks to JL for such a detailed explanation while I was asleep !!. Pete On Feb 25, 9:43 am, RMP wrote: Thank you for your very detailed response. I made a few adjustments to the formula and found the errors. One error which i do not fully understand is that one of the cells I was evaluating contained a constant? Anyway, I have learnt a lot and have been able to get the formula's to work thanks to yours and Pete_UK's response. Thanks again, very much appreciated!!!! -- RMP "JLatham" wrote: I'll try to fill in for Pete_UK for a minute. The #N/A means that the value you were looking for was not found in the source list. In VLOOKUP() (as explained in Excel Help on the subject) Using the formula Pete gave for reference: the first parameter is what you are looking for (whatever is displayed in A2 on the current sheet) the second parameter is where to look for it, in this case it is going to look for it in column A of a matrix that goes from A1 to B1000 on Sheet1. The third parameter, 2 - says when you find a match for this, return the value in the second column of the lookup array. Finally, the ,0) says that your list in the lookup array, column A, does not have to be sorted in any particular order. I suppose the question at this point is, assuming you put the formula on Sheet2 in cell B2 exactly as Pete_UK gave it to you: What is in cell A2 on Sheet2, and is it definitely a match for an entry in Column A (rows 1 through 1000) on Sheet1 ?? Since you're getting #N/A, I'm assuming either nothing or a typo in A2 on Sheet2. Now, if the value in A2 on Sheet2 should NOT be on Sheet1 in cells A1:A1000, and you want to keep the #N/A from showing up, write it this way: =IF(ISNA(VLOOKUP(A2,Sheet1!A$1:B$1000,2,0)),"",VLO OKUP(A2,Sheet1!A$1:B$1000*,2,0))) that says that if when attempting to do the VLOOKUP an #N/A error takes place, just output an empty string ( ,"", in the formula), but if things worked ok, then show me the result. "RMP" wrote: Appreciate your help. I have set the spreadsheet and formulas up exactly as you have described below but i am getting the following erroe reference in cell B2 worksheet 2- #N/A. Are you able to describe what each part of the formula refers to specifically to help my understanding of it? Please could you break it down? Thanks -- RMP "Pete_UK" wrote: Assume your table with the account code and name is on Sheet1, occupying A1 to B1000. Assume the other sheet is called Sheet2, and has account code in column A and payment amount in column B. First of all, insert a new column B in Sheet2, and enter this formula in B2, assuming your data starts in A2 - maybe with a header row: =VLOOKUP(A2,Sheet1!A$1:B$1000,2,0) Adjust the ranges to suit your table. Copy this formula down column B for as many items as you have in column A. Hope this helps. Pete On Feb 24, 9:29 pm, RMP wrote: Please could someone help me with look up tables. I have 2 different worksheets within one spreadsheet. The one worksheet contains an account number eg 00 00000 000 with an account name in the column adjacent to the account number. The other shows just the account number and payment amount. Is there any formula i could use which looks up the account name from the one worksheet and adds the account name next to the number in another worksheet? I have thousands of entries to complete so any help at all would br brilliant. Thanks -- RMP- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup using 2 cells as the "X" and "Y" coordinates.... | Excel Worksheet Functions | |||
Combine logical formulas "if", "and", "or" | Excel Discussion (Misc queries) | |||
Combining formulas, "and" & "or" to verify content of multiple cel | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |