I need to find a forumula that will enable me to transfer values from a table within the worksheet. Something along the lines of if I13 = A16 then need to enter the number that appears in A17 into cell 019, if I13=B16 enter number in B17 into 019. I also need the forumula to include if the figure in 113 is less than 3 then need to enter 0 into 019.
Hope this makes sense and that someone can help!
Many thanks.
We need a better description of your table, i.e., column labels, how the row contents are related (sorted?), etc. Your example doesn't quite fit with the idea of "table". Even better would be a zipped sample.
Ben Van Johnson
sorry - I know I'm not being very clear and I don't know how to 'zip' a sample to you. I'll try and explain a little better.
I have a table in worksheet on 2 rows (A16 to V16 and A17 to V17). Whatever figure is in cell 113 I need to link it to the same figure in row A16 to V16 but then drop in the figure that appears below in row A17 to V17 into cell 019. Unless the figure is less than 3 in which case a '0' would appear in 019. Is this any clearer?!?!? I hope so!
I have a table in worksheet on 2 rows (A16 to V16 and A17 to V17). Whatever figure is in cell 113 I need to link it to the same figure in row A16 to V16 but then drop in the figure that appears below in row A17 to V17 into cell 019. Unless the figure in I13 is less than 3 in which case a '0' would appear in 019. Can anyone help?!?!?
I have a table in worksheet on 2 rows (A16 to V16 and A17 to V17). Whatever figure is in cell 113 I need to link it to the same figure in row A16 to V16 but then drop in the figure that appears below in row A17 to V17 into cell 019. Unless the figure in I13 is less than 3 in which case a '0' would appear in 019. Can anyone help?!?!?
I have a table in worksheet on 2 rows (A16 to V16 and A17 to V17). Whatever figure is in cell 113 I need to link it to the same figure in row A16 to V16 but then drop in the figure that appears below in row A17 to V17 into cell 019. Unless the figure in I13 is less than 3 in which case a '0' would appear in 019. Can anyone PLEASE help?!?!?
Sorry to be thick, but if if A1 is same value as B1 how do I put value of C1 in D1?!! I need to add a few of these instructions to one cell (eg D1) for a range of cells - is this possible?
Thanks in advance for your help!
Put this formula in cell 019:
Please Login or Register to view this content.
You Are An Absolute Super Star - Truely The Best!!! Thank You So Very Much!!!!!!!
#2: Put this in D1:
Say: If A1 value equals B1 value, then put C1 value in D1, else put nothing ("") in D1.Please Login or Register to view this content.
The HLOOKUP works by scanning each column of the first row of your table (either A16:V17 or A59:N60) for a value matching I13 (F56). If it finds a match, it fetches the value from the second row (in this case) of the same column. So:Just wanted to thank you so much for your time and help. There is one more thing...
I tried to transfer that formula elsewhere in the table and it doesn't seem to like it.
I used F56 instead of I13 and table ran from A59 - N59 and A60 - N60 with formula to go in N62. Any suggestions? ... Many thanks.
will scan A59 to N59 for a value matching F56 and fetch the value from the column of A60 to N60 in the matching column.Please Login or Register to view this content.
The forumula you gave me works and is fantastic, but for some reason because the figures run like this on first row:
<0.3 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1.0 1.1 1.2 1.3 1.4 1.5+
and this on second:
0 2.75 3.00 3.25 3.50 3.75 4.00 4.25 4.50 4.75 5.00 5.25 5.50 5.75
putting in '0' for figures <3 doesn't work - it just shows up as N/A if F56 has figure less than '0' in.
Because I don't fully understand how the forumla works I can't correct it.
I'm so sorry, I know I'm being a pain and I'm sure you'll be glad when I've logged off - which will be very soon I promise you!!
If you have a solution I would welcome it, but if not I want to thank you again for all your help tonight. You've been very patient.
I only used "IF I13 < 3" as a test because that was the condition you gave in your first example.
You should be able to replace the "3" with whatever cutoff value you choose, say 0.25, for example.
The first formula is just an if..then..else structure, thus: If the value of I3 is less than some cutoff value then just put 0, else look up the value and fetch the table entry.
Last edited by protonLeah; 03-30-2008 at 07:12 PM.
Well, now I feel less guilty about asking you another question!
I have replaced the formula with: =IF(F56<0.3,0,HLOOKUP(F56,A59:N60,2,0)) and this works .......... UNTIL I want to count anything >1.5 as 5.75. Any words of wisdom to include this into the above formula?
Thank you once again. I really feel at this stage I should send some wine to say thanks!!
The formula should work for all values from 0.3 and up, unless you mean that you only want to match values greater than some floor value. In that case, the test would be IF(F56>= 1.5, ... (or whatever your smallest desired value is).
Say you want to find values in the range 1 to 10, then you would use something like:
=IF(AND(F56 > 1, F56 < 10), ... to
Among other possibilities.
Please can anyone help.
I have this forumula is cell N62:
=IF(F56<0.3,0,HLOOKUP(F56,A59:N60,2,0))
I need to add into the above formula if cell F56 = >1.5 then it is to be counted as 5.75. I can't get my head around this one at all and can't figure out where to put it and how it should read so the formula is recognised (e.g. with comas in right place etc!).
Any help would be appreciated.
The basic if..then..else formula has three parts separated by commas:
the test
the "then" (ie. true), and
the "else" (ie. false).
However both the then/true and the else/false parts can have their own if..then..else formulas NESTED
So, for your example:
"=IF(F56<0.3,0,HLOOKUP(F56,A59:N60,2,0))" with an additional test for F56 > 1.5 becomes:
Read: If F56<0.3 then enter zero, else, if F56>1.5, then enter 1.5, else look up the F56 value in the table.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks