Ok here is my problem. I am to have all of figures in sheet two and sheet one will be a spread sheet to fill out. Basically in the field of B4 on sheet one they will enter a dept number. In the field of B8 it will pull the figure from sheet two based on the figure they entered. Example of an IF formula I tried to use:
IF(B4 = 34, Sheet2!B34, IF(B4 = 35, Sheet2!B35, IF(B4 = 36, Sheet2!B36,IF(B4 = 37, Sheet2!B37))))
Ok that works find but I have 98 different dept numbers they can enter to pull figures from. Problem is on my computers at work they have an older excel on it and it is limited to the number of nested formulas can be used.
As you can see depts are in order on Sheet Two. Anyone know of an easier formula I could use to get this to work on Excel? I am sure there has to be a way.
Thank you so much!
2007-11-05 06:20:44 · 6 answers · asked by thepick48 2 in Computers & Internet ➔ Software
Use Vlookup(). It will look up the value in B4 on Sheet2 and return the appropriate value from Sheet2.
in B8 enter:
=Vlookup($b$4,sheet2!$b$1:$C$x,2);
where x = the last row of departments on sheet2.
On sheet 2, beginning in cell B1, enter the departments. In cell C1, enter the values associated with that department.
This will give you the result you want.
2007-11-05 06:40:25 · answer #1 · answered by ianmacpherson55 3 · 0⤊ 0⤋
1
2017-01-21 21:20:10 · answer #2 · answered by anthonyt 4 · 0⤊ 0⤋
Pulling Data From Excel
2016-11-07 07:57:57 · answer #3 · answered by ? 4 · 0⤊ 0⤋
If you want to pull the data from the second sheet based on the names, I have to assume a few things: Sheet 2 - column A contains names, Column B contains gift amounts Sheet 1 - column A contains names Your formula would look like this: In column B, Sheet 1 (first cell with a name next to it) =INDEX(Sheet2!B:B,MATCH(Sheet1!A1,Shee... To briefly explain... INDEX - function tells excel that the data you want to pull from is located somewhere in Sheet2!B:B Sheet2 - name of the sheet from which you want to pull the data B:B - column the data you want to pull is in (could also be a specified range, like B2:B35 or whatever) MATCH - tells excel you're looking for something specific (you're looking for the data in Sheet1!A1) Sheet1!A1 - this is the data you're using to locate the data you want to pull in (using the name of the person to find the amount they donated) Sheet2!A:A - tells excel that the data you're looking for in Sheet1!A1 should also be found somewhere in Sheet2!A:A 0 - this means that excel will only return a value if it finds and EXACT match
2016-03-13 23:43:01 · answer #4 · answered by ? 4 · 0⤊ 0⤋
You would do this with INDEX:
=INDEX( Sheet2!B34:B134, B4-33 )
I subtracted 33 instead of 34 because the index value starts with 1, not 0.
2007-11-05 06:27:38 · answer #5 · answered by Neebler 5 · 0⤊ 0⤋
Try using either a VLookup or HLookup. Way easier and simplier
2007-11-05 06:37:51 · answer #6 · answered by AJ 7 · 0⤊ 0⤋