Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

INCREMENT VLOOKUP

Hello. I am trying to drag a VLOOKUP function across a row and it works ok other than incrementing the column index number.

I have used the following:

=VLOOKUP($A3,'Data Drop'!$A:$GA,2) and when dragged it returns the following in the next column:

=VLOOKUP($A3,'Data Drop'!$A:$GA,2) instead of 3...4.....5 etc.

I have also tried:

=VLOOKUP($A3,'Data Drop'!$A:$GA,COLUMN()) but I dont know what this does and it returns some strange answers!!!

Any ideas?

Thanks


Post your answer or comment

comments powered by Disqus
I have an issue with excel right now and I was hoping someone could help me here.

I have a worksheet:
customer# | purchase amount	| paid?
1000      |   $123          |  Y
1000      |   $234          |  Y
1000      |   $345          |  N
1001      |   $9999         |  N
1002      |   $200003       |  Y
1002      |   $20054        |  Y
1002      |   $200566       |  Y
1002      |   $201234       |  N
1002      |   $200003       |  Y
1002      |   $205656       |  Y
1002      |   $200003       |  N
1002      |   $202345       |  Y
1002      |   $265666       |  N
1002      |   $209999       |  Y


And I need to output like this:


customer# | purchase 1 amount | purchase 1 paid? | purchase 2 amount | purchase 2 paid? | purchase 3 amount | purchase 3
paid? | etc...
1000      |   $123            |    Y             |   $234            |     Y            | $345              |  N
1001      | $9999             |    N
1002      | $200003           |   Y              |   $20054          |       Y          |  $200566          |  
Y
can anyone help me out with this? I'm confused because I don't know how to do an incremental vlookup.

Hi,

Is it possible to use Vlookup and Hlookup to search for its value over
multiple worksheets in a different workbook. If so could you please
give me an example.

Also sometime ago I saw a post giving an example of an incremental
Vlookup formula which did away with the need to manualy change the
colunm index num.

Thanks for any help

Don

Hi

Search did not find what I was looking for

I am using the vlookup function and am pasting across columns.
Is there a way to increment the column number argument?

e.g. Cell A3 = vlookup($A1,data,1,false)
when copying across,
cell A4 = vlookup($A1,data,1,false)
but would like it to be vlookup($A1,data,2,false)

Guess there must be a stupidly easy way of doing it!

Thanks

I have the following formula written to my worksheet in VBA. I need to increment the cell reference of the VLOOKUP crieteria as I copy this formula around the worksheet.

So all the formulas in Column F would be
=(VLOOKUP($C8,hide_NBSRecCost!$C$8:$HN$250,4,FALSE))
Then column G would be;
=(VLOOKUP($C8,hide_NBSRecCost!$C$8:$HN$250,5,FALSE))

Is there a way that I can do this please?

I have the following formula. How can I change it so thst when copy/drag the column number automatically increments by 1

IF(ISNA(VLOOKUP($A2,'Purchase Order Pivot Table'!$5:$500,67,FALSE)),0,VLOOKUP($A2,'Purchase Order Pivot Table'!$5:$500,67,FALSE))

macke

Looking for some help in order to increment a cells value by one depending on how many seperate rows there are for a specific instance. Ill try and demonstrate rather than babble.

Report 1(Spreadsheet 1)

Employee# Reason
123456 Late
123456 Late
654321 Late
654321 Late
654321 Late

This example shows two seperate employees, the first was late twice, the second three times. As of right now I have a formula that will return a value in numerical form but I need it to increment for each time an employee is late. The output is on a seperate speadsheet and based on the example above should read like this:

Employee# W/E 3.10.12
123456 2
654321 3

Here is the formula I have without a way of incrementing. I appreciate any help.

=IFERROR(IF(VLOOKUP(B3,[3.10.12.xlsx]3.10.12'!$E$6:$I$33,5,FALSE)="Late",1,""),"")

Looking for some help in order to increment a cells value by one depending on how many seperate rows there are for a specific instance. Ill try and demonstrate rather than babble.

Report 1(Spreadsheet 1)

Employee# Reason
123456 Late
123456 Late
654321 Late
654321 Late
654321 Late

This example shows two seperate employees, the first was late twice, the second three times. As of right now I have a formula that will return a value in numerical form but I need it to increment for each time an employee is late. The output is on a seperate speadsheet and based on the example above should read like this:

Employee# W/E 3.10.12
123456 2
654321 3

Here is the formula I have without a way of incrementing. I appreciate any help.

Thanks,
Steve

=IFERROR(IF(VLOOKUP(B3,[3.10.12.xlsx]3.10.12'!$E$6:$I$33,5,FALSE)="Late",1,""),"")

If I have =VLOOKUP(date,data,2) and in the next cell down I want
=VLOOKUP(date,data,5) so the col_index_num increments by 3 each time how
would I go about doing this so that it can be copied down instead of editing
each line by hand?

Many Thanks in advance

I've to iterate through each cell in "Column C" in downward direction.

I've to fetch values from the corresponding cells for which I'm using vLookup. I need to fetch multiple values so I would be using more vLookups in the loop after I get this working.

All these fetched values would be clubbed and based on conditions counters ,like one of them being "A" would be updated and summed at last to find the sum of each counter variables.

Here I'm trying to do is use loop for application "VLookup", but for some reasons it is not working.

I've tried using
..VLookup( Range("C" & i), ...
..VLookup("C" & i, ...
..VLookup(Cells(i, "C"), ...

i is the counter and C is the column. Could you please help me with it? I've been searching for help from past 8 hours. Finally I had to paste. I'll attach the file too.


	VB:
	
 CalculateApril() 
     
    Dim i As Integer, A 
    A = 0 
    Dim isDecomm 
    Dim myRange As Variant, mySelectedArea 
     
    mySelectedArea = ThisWorkbook.Worksheets("MSL").Range("C2:G22") 
     
    For i = 2 To LastCellInColumn - 1 
        With ActiveSheet 
             ' Define Range
            myRange = "C" & i 
             '   Issued in April. Checks for the Month
            If Range(myRange).Value = 4 Then 
                A = A + 1 
                 '   Are they Decommissioned ? Yes = Ignore, No - Count
                isDecomm = Application.VLookup(Cells(i, "C").Value, Range("C2:G22"), 5, False) 
                Debug.Print Cells(i, "C").Value 
                 'MsgBox isDecomm
            End If 
        End With 
         '   Increment i/Row
    Next i 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have two colms Rank(colm L start cell2 - numbers 1-50) and StateName (colm M start in cell 2). The colms (B-K- how many colms needed is decided by a formula in colm A and will populates a room number in B1-k1) If B1-K1 are populated I have to look at rank to find 1, 2, 3, 4, .....50. Once found it has to place the Statename in colm M in the cell the formula is in B2, c2, ....K2. Here is what I have thus far:

Code:
=IF(B1 " ",VLOOKUP(1,$Q2:$R51,2))

This gives me the desired results for B2 (where the formula starts) but here is my questions. When I copy the cell to C2, D2 etc. it keeps the 1 in the vlook up and I need it to look for the next rank. So for C2 I need to see VLOOKUP(2,$Q2:$R51,2)) and D2 VLOOKUP(3,$Q2:$R51,2)) and so on.

Thanks in advance for your help!

Looking for a formula (what if, vlookup, etc) that will give me the number of trades required for a given starting capital sum (B5), risk % (B6) inorder to approx. attain the figure shown in C17 (=> by up to £500). The compound trades based on incremental increases in trading capital are shown on the lookup table.

ie something that will (depending on which ROT I am looking at Min, Max, Average) will look up the column in the look up table and then when the condition above is met insert the corresponding trades figure in the table F, H, J 9-10.

Thanks for your help and/or suggestions.

Murray Howe

Just a quick one as i am sure it's fairly simple however the answer eludes me.

I have a staff holiday and absence worksheet that contain 94 staff over 12 months, each staff record is further linked to an individual record. As you can imagine there is a hideous number of links to create. I am wandering if there is a way to simply link 1 sheet then copy with the link values incremented by 1 Row.

If i have not explained myself clearly enough then please let me know.
i have attached a copy of the two files to give you an idea of why it's not easier.

I realise that the easiest way would be to simply use vlookup to generate the individual report but i have instructions that individual records have to be kept. The design could i guess be changed as long as it fitted on 1 sheet without any tabs. The reason being is that it is used to look at historic patterns.

Any ideas?

I want to copy =sum(a2:a3) across 5 columns incrementing the row by 1 per column... then I want to copy the formulas down 10 rows... so changes would only occur when the formula is copied across columns... ie Column A would only sum a2:a3, a3:a4, a3:a5 etc... Column B (incremented by one row) would contain sums a2:a4, a3:a5, a4:a6 etc...I know how to do this by hand but I use a vlookup function that uses these formulas that use 250 columns by 3000 rows so I am interested in learning how to change the copy over to next column without having to Cntrl F all 250 columns... Simple example... formula is copied across columns incrementing row by 1 and then has ability to copy formula down to still have the same functionality as the example.Column A
=SUM(A1:A2)=SUM(A2:A3)=SUM(A3:A4)=SUM(A4:A5)=SUM(A5:A6)

ColumnB
=SUM(A1:A3)=SUM(A2:A4)=SUM(A3:A5)=SUM(A4:A6)=SUM(A5:A7)

ColumnC
=SUM(A1:A4)=SUM(A2:A5)=SUM(A3:A6)=SUM(A4:A7)=SUM(A5:A8)

ColumnD
=SUM(A1:A5)=SUM(A2:A6)=SUM(A3:A7)=SUM(A4:A8)=SUM(A5:A9)

I am having a problem with a vlookup in excel. I need it to find the nearest match but due to my table, it is incorrect. It should be finding the lower figure if the match isnt exact. My figures are as follows: (they are incremented inbetween)

98% = 100%
100%= 75%
105% = 0%

when this is sorted in ascending order, it no longer looks for the lowest number but instead looks at the number before it, eg, 98.1% will pick up 100% instead of 99.5% etc. any suggestions

Hello all. I have found the message boards to be a great resource for many projects. This new one has me a bit stumped. I have a data set that labels rows using the year and quarter - 20071, 20072, 20073, 20074 for example (calendar year 2007 quarters one through four). In one instance, I need to identify data from the last four quarters. I am using vlookup but am unsure how to structure the formula when I start at quarter two and have to work back to the prior year.

My title seems a little long - I have spent much time searching the net for what I can only assume is a simple answer.

I have created a UserForm for entering data onto my sheet. Most of the cells in the sheet use formula derived from the data in about 6 boxes the problem I am having is I don't know how to make my formula increment the cell values. I have included the relevant code below.

Many thanks,

.Offset(RowCount, 0).Formula = "=VLOOKUP(I234,Z$1:AA$86,2)"

When my macro runs it simply copies the formula exactly and does not change the value I234 to the next row down - It is treated as an absolute.

I am using Visual Basic (same as VBA) to insert a formula into a worksheet using a incremented variable for the cell adderess. This works for a SUM formula but I can't get a VLOOKUP to work. Like this:
'xlbook.Worksheets("BOM").Range("E" & FirstCell).FormulaR1C1 = "=VLOOKUP(A" & FirstCell & ",Inventory!A1:J2314,4,FALSE)"

The addresses get pasted right but they are inclosed in single quotes. #NAME is returned.

Any ideas?

Is there a way to copy a vlookup formula and have the "value column" increase incrementally?

I have a list of people that are working particular shifts which are set up
in four to five hour increments. Some people are working consecutive shifts
(working eight/nine hours in a day). I am trying to show each person's
schedule with beginning and end times using VLOOKUP. It is working just fine
if the people only work one shift per day. The multiple shift people only
have their first shifts display. I would like to LOOKUP based upon the
person's name giving the first shift's start time and the second shift's end
time and haven't found a good combination of functions to use. Any
suggestions?

Hi

Anyone know how to fix this problem?

I have SHEET1 with a date/time in cell A1 and then a formula to add one hour
to the cell from the row above (for cell A4 the formula is =+A3+(TIME(1,0,0))
to generate a colum of date/times that are 1 hour increments.

On SHEET2 I start with the same date/time in cell A1 (from A1 on SHEET1) and
add several hourly values to it, several times. For example row 5 contains;

cell A5 contains "+G4"
cell B5 contains "=+A5+TIME(J5,0,0)" where J5 contains 0
cell C5 contains "=+B5+TIME(K5,0,0)" where K5 contains 1
cell D5 contains "=+C5+TIME(L5,0,0)" where L5 contains 2
cell E5 contains "=+D5+TIME(M5,0,0)" where M5 contains 6
cell F5 contains "=+E5+TIME(N5,0,0)" Where N5 contains 10
cell G5 contains "=+F5+TIME(O5,0,0)" where O5 contains 8

The next row references G from the previous row. So;
cell A6 contains "+G5"

and so on.
The problem is that if I use Vlookup or Index/Match to search column B on
SHEET2 to match a value from column A on SHEET1, I can't get an exact match.
=INDEX(SHEET2!$B$1:$I$501,(MATCH(A30,SHEET2!$B$1:$ B$501,0)),8)

If I actually type the date/time (that is calculated and displayed in cell
A30) into A30, (type "1/1/06 10:00 AM" into A30) the correct result is
returned from the Index/Match.

I tried changing the Match_type to 1 but then every date/time from SHEET1
returned a match if it was greater than the date/time in SHEET2 but less than
the next day.

Thanks

Norma

I have a large table where the column A is also the lookup column.
Basically, I'm doing a moving average over column A. In excel 2003, the
following works

=VLOOKUP(A264-3000,A:A,1)

In Excel 2007, I get #VALUE!. How can I use column A as the lookup source
and the result column?

For those of you interested, 3000 represents mileage (oil change
increments).

Thanks,
Mike Ober.

I am trying to create a workbook that creates a copy of a existing worksheet
(for the template), then I paste in information that the arragement never
chages. From the information I pasted in I want it to populate a separate
sheet with the values I selected. The problem I have is is there a way to
auto increment a sheet name within a formula.

The new sheets I copy and create will have the names, Report 1, Report 2,
Report 3 and so on. Is there a way I can make a formula so I can auto fill,
i.e. cell A1 is from Report 1 and cell A2 is from Report 2.

Right now my formula looks like ='Report 1'!A$89

I have also been experimenting with =VLOOKUP('Report 5'!$A$89,'Report
5'!A$89:O$89,1)

Thanks

I have a formula that I want to copy across different worksheets in a
workbook but it keeps incrementing values that need to remain constant.
The formula is as follows:
=VLOOKUP(A95,Items!A2:B500,2,FALSE)
And the part of the formula that I don't want to change is "A2:B500" (Items!
being alpha remains as does "2,FALSE")
I've tried using paste special but have not found the right combination. Is
there a wilcard character that can be used? I've seen the use of $ but am not
sure of the ramifications and where/when to use it.
Currently, I'm having to manually edit each formula but there's got to be a
better way!
Thanks in advance for any help you can offer,
-jt.

I am getting a #N/A when I try to use a variable to represent a table array.
The table array incluses a network path. It looks like this:

=IF(ISNA(VLOOKUP(A29,'S:CLIENTSPTracking2005_T rackingDaily[Through
7-13-05.xls]how_ordered'!$A:$F,6,FALSE)),0,VLOOKUP(A29,'S:CLI ENTSPTracking2005_TrackingDaily[Through 7-13-05.xls]how_ordered'!$A:$F,6,FALSE))

This formula does work on it's own. What I am trying to do is be able to
change the date automatically. First, I have a row with all the date in a
m-d-yy format. I then use the TEXT function on the date. Next I use
SUBSTITUTE to change 7-14-05 to 7-14-05. This resides in cell U9. When I
use VLOOKUP(A29,U9,6,FALSE) or when I use VLOOKUP(A29,INDIRECT("U9"),6,FALSE)
I receive a #N/A error. If I remove the " " from the idirect statement, I
get a #REF error. I need to be able to increment the U9 when I copy
horizontally ( i.e. V9, W9, X9,...) Any help would be appreciated.

I'm having an unusual problem with Vlookup. I enter the formula in a cell and it produces the correct result. However when I copy down the displayed result is exactly the same as in the original cell. When looking at the formulas I note that the lookup cell has been incremented correctly. for example:the original formula is:
dragging this down the next row formula is:
and so on. However the result for each cell is the same as in the top one. I have noticed however that if in the formula I
manually delete 'A3' and type in 'A3' then it produces the correct result so obviously something is going wrong when draging
down the formula even though it appears to be correct

any idea's?


No luck finding an answer? You could always try Google.