Vlookup Returning The Same Value 
Vlookup Returning The Same Value  Excel 

Many Thanks
Karamazov
Similar Topics
I have the following in G2:
=VLOOKUP(A2,Sheet1!A:D,4,FALSE)
The formula returns the correct result, which in this case is a number  2
When I fill down my range, the formula copies correctly, but every result is the same. 2.
However, if I go to the next cell down (G3) and activate it by pressing F2, and then hit Enter, the result changes and is now correct.
Any ideas whats going wrong?
But it gets even more weird. When I click on the Column C cells and then it shows the formula up top in the formula bar and if I put my cursor anywhere in the formula bar and hit Return the formula does not change however the correct value then appears in the Column C cell. It is like the act of putting the cursor in the formula activates it to work properly but until it is activated that C cell shows the value of the cell which it was dragged down from.
This is quite bizarre. Has anyone ever seen this before? I have no idea what is going on. I ran a scan for viruses and none were found. I tried it on several new/different spreadsheets but it keeps happening.
Thanks for any tips on this.
Example:
A1: 50
A2: 10
B1: 60
B2: 20
A3: Formula: =A1+A2 Displays: 60
Right click A3, Copy, right click B3, paste
A3 displays 60
When I click save, it will change the display value to 80.
I am trying this on his workstation and mine. Mine has Office 2010, so I think there might be an issue with the file itself.
Also, not just copy and paste. I can also just click the top cell after filling in the formula and then drag the bottom right of the cell downward and it will do the same of filling in the correct formula, but have the incorrect value.
I know that I could get him to just click save each time before really looking at the results, but that is just a band aid to the problem.
Any ideas how to fix this?
Cells accept hours over 23,
Adding cells in column returns correct total time.
Have not found a way to multiply these cells by a $ hourly rate.
So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced
cell  then use these values to calculate total payment for $rate per hour.
However, the HOUR(cell ref) formula returns the hours in excess of 24 when
the cell contains an hour value in excess of 23 (ie 27 hours returns 3).
How do I enter the value in a cell (say B4) into a formula as a constant so it doesn't change when I copy it down the column?
For example,
B4 = 2.4 (a constant)
This is the result I want:
F6 =C8 + B4*4.243
F7 =C9 + B4*4.243
F8 =C10 + B4*4.243
B4 remains constant as I copy F6 down.
What's the correct formula for F6 so I can copy it down and keep B4 fixed?
Thanks.
If I double click the cell and then hit enter it will retotal the area and then give me the correct value, but I'd obviously rather is just work right the first time.
click double click on the cell before the formula recognises the account
number. I have to do this for each cell. I have tried different formats. It
is not saving me that much time.
Are there any ways around this so that it updates upon dragging the formula?
Cell B5: start time: 4:15 formatted as 4:15:00 am
Cell B6: finish time: 6:15 formatted as 6:15:00 am
Cell B7: total time: 2:00 formula in cell: =TEXT(B6B5,"h:mm")
Cell B8: total batches processed: 22 (this is entered manually)
Cell B9: batches per hour: formula in cell: =B8/TEXT(B7,"h")
as long as I have this formula in cell B9 the answer comes out correct which should be 11 per hour.
If the formula in B9 is B8/B7, the answer is 264.0, Is this because of the way excel is reading the total time or the total time is formatted as a time, not an actual number? Is this the correct way to solve the problem?
answer should be 11 per hour.
triangle. I copied it to create a new data set and used find &
replace to change the worksheet references to the new ones.
The cells still contain the result of the old formula referring to the
previous worksheets. The only way I can get the formula to return the
correct result is to edit (F2) each cell and press enter. Calc now
(F9) does nothing.
I've seen this before, but this time, I need to calculate many
thousands of cells and don't have time for this workaround.
Any ideas?
Thanks.
Don S
I want to know how to copy part of a cell and paste it into a new cell.
Example:
a1= 8hAs7c
portion needed: 7c
I used this formula:
=RIGHT(A1,2)
This returns 7c like I want.
However in the next cell I want to source the middle two (As)
And in another cell I want the first two (8h)
How do I go about this?
It's for a house cleaning price list, with two sets of prices. I want the formula to say if the value in E7 is R, i want to lookup the value for the house in the second column. If the value in the cell is anything else, i want to lookup the value for the house in the third column.
So in sense, my formula should look like =IF (E7=1,"=VLOOKUP(A10,'Clean Prices'!$A$2:$C$58,2,FALSE)","=VLOOKUP(A10,'Clean Prices'!$A$2:$C$58,3,FALSE)")
But it doesn't work. Is it possible to nest a vlookup in an if statement?
E.g.
Manchester United 3  0 Arsenal
Prediction: 4  1
Points: 1
I've found a formula online that works for most scores; the correct score (e.g. 3  0), a correct win (e.g. 4  1), and in the case of a draw (e.g Man Utd 1  1 Arsenal, and the prediction 2 2), correct draw (but not the correct score). The points are as follows: Correct draw (Prediction = result): 4 Correct win (Prediction = result): 3 Correct win, incorrect score (Winner correct, score not): 1 Correct draw, incorrect sco 1
The formula I found online for all but the correct draw is: =IF(A3&B3=C3&D3),3,IF(AND(A3=B3,C3=D3),1,IF(AND(A3>B3,C3>D3),1,IF(AND(A3<B3,C3<D3),1,0)))))
Could someone help me work out how I can write a formula to add before the above that will check the contents of four cells (the home score, away score, home prediction, away prediction), and if the number in all 4 cells are the same, allocate 4 points? I've tried using averages, but some combinations would void this. I've also used the SUM function, but the same happens, as with MULTIPLYING.
Something like =IF(A3:D3) are equal, 4,0
Below are a few examples of what I mean:
(Result) (Prediction) (Points)
A B C D E
3  3 3  3 4
2  1 3  1 1
1  0 1  0 3
3  3 2  2 1
Apologies in advance if I haven't explained this clearly enough.
Thanks
Will
Is there a formula so that when I drag C1 horizontally into D1, E1, F1, ..., the values placed in each cell will be =A2, =A3, =A4, ...
I do not want to transpose the values from column 1 into C1, D1,.... I want these cells to have a formula that links them up to column 1's values
Thanks
(changes the cells it should subtract), however, the result stays the same.
It matches the formula I copied it from, even though the cells to calculate
are now different. I even did a paste special and said only formula, but
still, same result. If I actually type in the formula, it works fine, but I
have a lot I need to copy and paste. Is there a way I can fix this?
Thanks,
I have formulas to add a number of days to a cell containing an entered date and display the resulting new date (ie. 03/01/2011 (c34)+11 = 03/12/2011)
I am seeking to leave resulting formula cell blank until a date is entered in the input cell. Currently when the input cell is empty the formula cell obviously displays 1/11/1900 using the above example.
What conditional format would achieve leaving the formula cell blank until date data in entered into the source cell?
Hopefully a simpler question for your experience level than mine.
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?
=SUMIF(D3:D13,"criteria",D3:D13)
I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)
It might just be that I don't know what this means in Help:
"color" > 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).
Can anyone help?
I am hoping for some assistance. Here is what I have....
I am trying to get counts of certain values.
This is in the same workbook but different sheets.
On sheet 1 is where my formula lies. In a cell, I want it to look at sheet 2 column B and look for instances of "A", then I want it to look in the same row it found "A" and look at the value in column "N" and evaluate if there is an amount. If there isn't skip it and don't count it, if there is a value count it if it falls within a given range.
An example of the formula is below...what am I missing?
=COUNTIF(Sheet2!B:B,(VLOOKUP(A,Sheet2!B:N,13,FALSE)=0:500))
=COUNTIF(Sheet2!B:B,(VLOOKUP(A,Sheet2!B:N,13,FALSE)=501:1000))
Thank you in advance for your assistance.
I'm trying to extract all the text in a cell which is on the right side of a comma (,)
Currently I am using this formula,
=RIGHT(C1,FIND(",",C1)1)
However it sometimes is missing some of the data after the , and sometimes pulling a few characters before the ,
any suggestions on how to correct my formula please?
Thanks in advance.