View original (French)

Excel Using VLOOKUP and INDIRECT

Solved
Latrix Posted messages 5 Status Member -  
Latrix Posted messages 5 Status Member -
Hello,

I want to perform a VLOOKUP on a range of cells from another non-generic sheet. For that, I'm using the following formula:

=VLOOKUP(I6, INDIRECT("'"&F6& "'!A5:G100"), 7)

- I6 contains the value to look for
- F6 contains the name of the sheet where the search should be made
- A5:G100 is the range where I want to search for the value in F6
- 7 is the column I want to retrieve

My problem is that this formula consistently returns the 7th column of the last row in the range.
(For example, if I replace G100 with G50, I retrieve the 7th column of row 50 even though the value I'm looking for is in G25).

Thank you in advance for your help.

6 answers

Answer 1 / 6
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
 
re,
=VLOOKUP(I6,INDIRECT("'"&F6&"'!E5:G100"),3,0)

vlookup "search" (...) in the first column to the left of the source table; since you had marked A5:G100, it couldn't find "extension" hence the unfortunate NA
Michel
8