Vlookup Formula question
- Thread starter Rlopez24
- Start date
In M24 you have the data and you need te have corresponding result at column 1 (Formula in N24)and wish to drag formula to data at N25 corresponding to column 2
At N24, this formula
here column 2
At N24, this formula
Code:
=VLOOKUP($M$24,$A$1:$X$200,ROWS($M$23:$M24),FALSE) here column 1
Darg it to N25 you will have =VLOOKUP($M$24,$A$1:$X$200,ROWS($M$23:$M25),FALSE)
SirJB7
Excel Rōnin
Hi, Rlopez24!
Regarding your question and considering that you want to vary the Col_index_num, I should consider slightly updating mercatog's formula as in this file:
https://dl.dropbox.com/u/60558749/Vlookup%20Formula%20question%20%28for%20Rlopez24%20at%20chandoo.org%29.xlsx
Using the cell reference of his example:
For dragging horizontally (columns) you should use this:
=BUSCARV($M24;$A$1:$X$200;COLUMNA()-COLUMNA($M24)+1;FALSO) -----> in english: =VLOOKUP($M24,$A$1:$X$200,COLUMN()-COLUMN($M24)+1,FALSE)
For dragging vertically (rows) you should use this:
=BUSCARV(M$24;$A$1:$X$200;FILA()-FILA(M$24)+1;FALSO) -----> in english: =VLOOKUP(M$24,$A$1:$X$200,ROW()-ROW(M$24)+1,FALSE)
In the sample file the vertical starts at row 27 regarding Pauli exclusion's and Heidelberg uncertainity's principles
In both cases (H&V) note that you should fix (with the $ sign) the "other" part of cell references: column for horizontal and row for vertically results.
Regards!
Regarding your question and considering that you want to vary the Col_index_num, I should consider slightly updating mercatog's formula as in this file:
https://dl.dropbox.com/u/60558749/Vlookup%20Formula%20question%20%28for%20Rlopez24%20at%20chandoo.org%29.xlsx
Using the cell reference of his example:
For dragging horizontally (columns) you should use this:
=BUSCARV($M24;$A$1:$X$200;COLUMNA()-COLUMNA($M24)+1;FALSO) -----> in english: =VLOOKUP($M24,$A$1:$X$200,COLUMN()-COLUMN($M24)+1,FALSE)
For dragging vertically (rows) you should use this:
=BUSCARV(M$24;$A$1:$X$200;FILA()-FILA(M$24)+1;FALSO) -----> in english: =VLOOKUP(M$24,$A$1:$X$200,ROW()-ROW(M$24)+1,FALSE)
In the sample file the vertical starts at row 27 regarding Pauli exclusion's and Heidelberg uncertainity's principles
In both cases (H&V) note that you should fix (with the $ sign) the "other" part of cell references: column for horizontal and row for vertically results.
Regards!