Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
I'm trying to come up with a formula: In Workbook 1, my cursor is in L6, and
I want to look up the number in B6 of Workbook 1 somewhere in Col. B of Workbook 2, and then take the figure in M6 of Workbook 1 and divide it by the number that's in column D of Workbook 2 corresponding with the number in Col. B of Workbook 2 that it looked up. I hope that explains it. In a nutshell: =lookup B6 in Col. B of Workbook 2, divide M6 in Workbook 1 by the number in Col. D that corresponds with the number in Col. B that it looked up from Col. B of workbook 1. Whew! Don't know if that's any better of an explanation! Connie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Try this:
=M6 / VLOOKUP(B6,Sheet2!B:D,3,0) Hope this helps. Pete On May 16, 1:47*pm, Connie Martin wrote: I'm trying to come up with a formula: *In Workbook 1, my cursor is in L6, and I want to look up the number in B6 of Workbook 1 somewhere in Col. B of Workbook 2, and then take the figure in M6 of Workbook 1 and divide it by the number that's in column D of Workbook 2 corresponding with the number in Col. B of Workbook 2 that it looked up. *I hope that explains it. *In a nutshell: =lookup B6 in Col. B of Workbook 2, divide M6 in Workbook 1 by the number in Col. D that corresponds with the number in Col. B that it looked up from Col. B of workbook 1. *Whew! *Don't know if that's any better of an explanation! * Connie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Pete, thank you. I'm getting #N/A, however. I put these two worksheets in
the same workbook, so the only change I had to make was change Sheet2 in your formula to Sheet1. Connie "Pete_UK" wrote: Try this: =M6 / VLOOKUP(B6,Sheet2!B:D,3,0) Hope this helps. Pete On May 16, 1:47 pm, Connie Martin wrote: I'm trying to come up with a formula: In Workbook 1, my cursor is in L6, and I want to look up the number in B6 of Workbook 1 somewhere in Col. B of Workbook 2, and then take the figure in M6 of Workbook 1 and divide it by the number that's in column D of Workbook 2 corresponding with the number in Col. B of Workbook 2 that it looked up. I hope that explains it. In a nutshell: =lookup B6 in Col. B of Workbook 2, divide M6 in Workbook 1 by the number in Col. D that corresponds with the number in Col. B that it looked up from Col. B of workbook 1. Whew! Don't know if that's any better of an explanation! Connie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
If you are getting #N/A this implies that there was no match found.
You could avoid the error by means of ISNA or ISERROR, but to check that the formula is working can you put a value in B6 which you know exists in column B of the other sheet? Pete On May 16, 5:07*pm, Connie Martin wrote: Pete, thank you. *I'm getting #N/A, however. *I put these two worksheets in the same workbook, so the only change I had to make was change Sheet2 in your formula to Sheet1. *Connie "Pete_UK" wrote: Try this: =M6 / VLOOKUP(B6,Sheet2!B:D,3,0) Hope this helps. Pete On May 16, 1:47 pm, Connie Martin wrote: I'm trying to come up with a formula: *In Workbook 1, my cursor is in L6, and I want to look up the number in B6 of Workbook 1 somewhere in Col. B of Workbook 2, and then take the figure in M6 of Workbook 1 and divide it by the number that's in column D of Workbook 2 corresponding with the number in Col. B of Workbook 2 that it looked up. *I hope that explains it. *In a nutshell: =lookup B6 in Col. B of Workbook 2, divide M6 in Workbook 1 by the number in Col. D that corresponds with the number in Col. B that it looked up from Col. B of workbook 1. *Whew! *Don't know if that's any better of an explanation! * Connie- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Peter, this works. I discovered that whoever prepared the other spreadsheet
put an apostrophe in front of all the numbers because some of them start with zero. The apostophe, of course, threw things off. I removed it and it now works. Thank you very much! Connie "Pete_UK" wrote: If you are getting #N/A this implies that there was no match found. You could avoid the error by means of ISNA or ISERROR, but to check that the formula is working can you put a value in B6 which you know exists in column B of the other sheet? Pete On May 16, 5:07 pm, Connie Martin wrote: Pete, thank you. I'm getting #N/A, however. I put these two worksheets in the same workbook, so the only change I had to make was change Sheet2 in your formula to Sheet1. Connie "Pete_UK" wrote: Try this: =M6 / VLOOKUP(B6,Sheet2!B:D,3,0) Hope this helps. Pete On May 16, 1:47 pm, Connie Martin wrote: I'm trying to come up with a formula: In Workbook 1, my cursor is in L6, and I want to look up the number in B6 of Workbook 1 somewhere in Col. B of Workbook 2, and then take the figure in M6 of Workbook 1 and divide it by the number that's in column D of Workbook 2 corresponding with the number in Col. B of Workbook 2 that it looked up. I hope that explains it. In a nutshell: =lookup B6 in Col. B of Workbook 2, divide M6 in Workbook 1 by the number in Col. D that corresponds with the number in Col. B that it looked up from Col. B of workbook 1. Whew! Don't know if that's any better of an explanation! Connie- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
You're welcome, Connie - thanks for feeding back.
Pete On May 16, 8:01*pm, Connie Martin wrote: Peter, this works. *I discovered that whoever prepared the other spreadsheet put an apostrophe in front of all the numbers because some of them start with zero. *The apostophe, of course, threw things off. *I removed it and it now works. *Thank you very much! *Connie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |