I want to filldown this formula, but the comparison range ( sheet 2, columns A & B, rows 4-73) never changes. how can accomplish this so when i use ctrl-d, only L4 updates (to L5, L6, L7, etc..), but the range remains the same?
by default, filldown tool updates the range to A5:A74, A6:A75, A7:A76, etc
if it helps i'm using this forumla to:
1) take a value (L4)
2) find this value in the first range (somewhere in sheet 2 column A)
3) enter the corresponding value (sheet 2 column b, same row)
I want to find a number in my array and return the value in column A. Trouble is if the array goes past column R I get the wrong Answer. the formula I am using is
=LOOKUP($C$22,$B$2:$AC$18,$A$2:$A$18)
Some cells are blank I am trying to draw a raffle.
Here is my data and formulas below:
The lookup of value 209 produces correct results when I look up as far as column R after that I get the wrong answer.
I am looking up values L, M, and S from a seperate worksheet based on two factors. age in months, and gender. I have read enough to figure out how to lookup values L,M,S one at a time using a vectorlookup, from one worksheet to another. However, the LMS values being looked up are for females only. I know I must get the gender codes worked in but not sure how to work this in. I don't know if I need an and If function, to meet the gender (1male, 2 female) or what.
I have = LOOKUP(REPT("z",255),CHOOSE({1,2},"",LOOKUP(C3,{1,2,3},{2,1,0}) )) in cell D3. C3=1 so I expected a result of D3=2. Instead I am getting a blank cell.
I am wondering if there is a way to drag a formula, for instance, down, and each cell that I drag down, it references one cell to the right.
Excel Example.png
I attached a photo -- I understand that this is an overly simplified example, but the idea is that it would reference the row as you drag down the column.
I want to use the V lookup function to return a value, but rather than returning the value in the same row as the match in the specified column, I want to return the value in the cell below.
Eg.
=VLOOKUP(B397, A$267:H$351, 7, FALSE)
... but rather than returning the value in column 7 on the row that matched with B397, I want the field below.
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows: =VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0) but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following: =VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE) The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
Is there a way to use the lookup function in reverse? The "lookup vector" on mine has to be descending, that is it it is a column going from high values at the top of the page to lower values at the bottom of the page. When using lookup function in this setup, excel gives me to wrong answer for some reason. If I make the lookup vector ascending, it works, but unfortunately the vector has to be descending in my worksheet.
it's possible to set a "key" for a vlookup so that one of the parameters of that key can be any value? For example if the data set key used in the lookup contains a concatenation of 4 parameters (a_b_c_d), is there anyway to modify the lookup array key so that the "c" parameter can be anything, ie. something like a_b_*_d ?
I am trying to do a vlookup where the values in the Table_Array have a string appended to them. For example:
I want to look up the string "Example1" and the lookup range is Example1 - monthly Example2 - weekly Example3 - weekly Example4 - monthly
So essentially I want vlookup to only look at the string before the hyphen. My initial thought was to do something like this: vlookup("Example1", left(A:A, 8), 1, FALSE)
What I want is that I have a table like below (but it's long for 52 weeks) and long down with Vlookups. I want the formula with which I can just do the copy-paste and it will work. W1, W2.... are the sheet names with exactly the same formats inside.
A B C D E
5
W1 W2 W3 W4
6 Sales 10 #N/D!
[Code] .......
The base formula (for W1) is: =Vlookup($a6;'W1'!$A:$B;2;0)
What I want, is the formula which instead of "W1" will write the sheet name which is in a row 5 (basically - cell name which is equal the sheet name), so with just dragging and moving the formula I will got the data from different sheets.
I tried this: =Vlookup(A6;'indirect("c5";1)'!$A:$B;2;0)
But I got #N/D! as in the example, instead of the numbers (yes, I put numbers into W1 and W2 sheets .
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?
I'm trying to do a Vlookup on a file that gets automatically downloaded to the computer from a website. The data is in lots of different data sets, like so:
Loans to countries Mar Apr May Jun
Loans to banks Mar Apr May Jun
Every month a new row of data gets added to each table, meaning the start and end cells of the array also shift each time.
I have attached the example workbook with the macros included but what I am trying to do is filter a column, then add in a formula to another column, then have the formula filled in to the end of the filtered selection.
At the moment I have to go over the last row as there may well be additions later. I recorded the macro, then tidied it up. All it seems to do at the moment is copy the Column header. So here is the macro:-
On sheet "Kilo" I have 2 command buttons one "insert" and one "delete". I would like the insert button to work as if it was being utilized as if it was still on the "Nom roll" sheet. If I use the button on kilo as is it messes up the Kilo sheet.
I am currently using this macro to fill down cells full of numbers which have been passed through formulae. It works for one input (C10), however I want it to work for another input (C11). Im trying to make it fill down the cells after a new set of headings identical to row 12. It has to take into account the rows used for the original fill down which relies on C10. I have tried to use the same macro twice, but I cant get it to do both layers. Basically I need a fill down of layer 1 (already done), and then a fill down for layer 2 after layer 1.
I have a auto filtered applied to 1000 rows of data and the actual quantity of rows I currently see is 55 rows. When I do a filldown command to apply this formula, "=CONCATENATE(X$1,I51)" the result I get is wrong becuase where the "I51" value is at the formula is picking up cell values that are hidden due to the applied autofilter.
What do I need to add to this formula so that it only picks up cell values that are not suppressed by the autofilter? I want it to disregard the hidden rows when I do a filldown and only apply the formula to the rows that are not hidden due to the applied autofilter.
I am trying something out the ordinary, instead of hardcoding the Range, I want o find the last used row of the Range and append, then filldown. I think my idea is good, but either syntax is off or not going to be allowed to do this.
I am running a fairly complex simulation at work and have quite a few different functions in it. For the simulation to work properly, i need to have the Solver add-in and the Analysis ToolPak installed. Everytime that i try and use the simulation on a different computer (or if anyone else tries to run it) the add-ins must be enabled again. Is there a way to set up a macro to enable them when either i open the simulation (first preference) or when a button is pressed (2nd preference).
What is the macro code to take a cell say H7 which has the formula =$K$3-F7, and filldown to the last row of data in column E? This needs to be dynamic as ill be running this from period to period so the number of rows will always change.
H7 will always be the starting cell for every period. Just not sure how to tell VBA to fill this formula down and stop at the last row of data with another columm as a reference.
