vlookup question concerning index column

[vvkelley]

Hello, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I had an excel problem that I could only partially solve in a recent project and was hoping someone here could tell me the better way of doing it.<o></o>
<o></o>
I recently had a project where I had to match the sample of physicians in one spreadsheet (call it sheet A, having about 300 rows of physicians) with that of a much larger spreadsheet B (about 10000 rows), which included sheet A's 300 physicians. My task was to pull data out the columns in sheet B and integrate it into sheet A. The rows in both sheets were arranged by unique ids in their first column so it was easy to use a vlookup to identify the rows I wanted in sheet b. Basically I would use =vlookup($A1,SheetB!$A$1:$Z$10000,2,False) and that would give me what was in column b of sheet b. I would drag that command vertically to the bottom of my 300 cases in sheet A and have the first column of data that I needed. <o></o>
<o></o>
My problem was that when I tried dragging the command horizontally to the right to give me the other columns I needed (basically I needed all of them) the index column number would remain fixed, giving me the same data. Is there a way to modify this command so that the index number automatically increases by one column when you drag it horizontally the way cell references normally do? I had little time and ended up retyping the index number but that wouldn't have worked with a larger sample (and I'm sure there's a smarter way of doing this). I was using Excel 2007, Windows 7 for all of this.
<o></o>
Thanks. <o></o>

 

[shg]

You could use something like

=vlookup($A1, SheetB!$A$1:$Z$10000, columns($A1:Me) + x, False)

... where Me is the cell in which the formula appears, and x is a fixed adjustment.

 

[repairman615]

Hello,

Welcome to the board!

Have you considered using the COLUMNI() function?

Try this in cell with empty row:

=COLUMN(A1)

then drag across to see this increases or decreases.
This could be incorporated for the index number.

Say you would like to start with 2:

=vlookup($A1,SheetB!$A$1:$Z$10000,column(B1),False)

 

[vvkelley]

That does the trick alright. Thanks so much for your reply, I appreciate it.

 

[T. Valko]

Hello, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I had an excel problem that I could only partially solve in a recent project and was hoping someone here could tell me the better way of doing it.<o></o>
<o></o>
I recently had a project where I had to match the sample of physicians in one spreadsheet (call it sheet A, having about 300 rows of physicians) with that of a much larger spreadsheet B (about 10000 rows), which included sheet A's 300 physicians. My task was to pull data out the columns in sheet B and integrate it into sheet A. The rows in both sheets were arranged by unique ids in their first column so it was easy to use a vlookup to identify the rows I wanted in sheet b. Basically I would use =vlookup($A1,SheetB!$A$1:$Z$10000,2,False) and that would give me what was in column b of sheet b. I would drag that command vertically to the bottom of my 300 cases in sheet A and have the first column of data that I needed. <o></o>
<o></o>
My problem was that when I tried dragging the command horizontally to the right to give me the other columns I needed (basically I needed all of them) the index column number would remain fixed, giving me the same data. Is there a way to modify this command so that the index number automatically increases by one column when you drag it horizontally the way cell references normally do? I had little time and ended up retyping the index number but that wouldn't have worked with a larger sample (and I'm sure there's a smarter way of doing this). I was using Excel 2007, Windows 7 for all of this.
<o></o>
Thanks. <o></o>

You can increment the column_index_number by using the COLUMNS(..) function.

Let's assume you want the first formula entered in cell B1 and then copied across the row.

=VLOOKUP($A1,SheetB!$A$1:$Z$10000,COLUMNS($B1:B1)+1,0)