VLOOKUP

[Connie Martin]

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

 

[Pete_UK]

Try this:

=M6 / VLOOKUP(B6,Sheet2!B,3,0)

Hope this helps.

Pete

 

[Connie Martin]

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]

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

 

[Connie Martin]

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]

You're welcome, Connie - thanks for feeding back.

Pete