Excel: Vlookup returning wrong values due to INDIRECT? 1

[rlee16]

[Task]: I am trying to add some data from another workbook to this Summary by using vlookup on the name of company (which is hard-coded, not a formula). Simple, I've done this a million times, nothing is wrong with the formula.

[Problem]: However, Vlookup is not returning the correct values.

[Background]: The worksheet is a a summary page that uses INDIRECT to compile data from many sheets.

[Question]:It this a matter of INDIRECT and Vlookup not working together on the same worksheet? Help, please.

Richard

 

[rlee16]

I would like to clarify that at first both the cell with the VLookup formula and the cells with INDIRECT formulas were accessing the name of the Company is pull in data.

(The Vlookup formula is trying to pull in data from another workBOOK, whereas INDIRECT formulas are pulling data from other worksheets within the ActiveWorkbook.)

However, I changed the Vlookup to use another cell to pull in the data but to no avail.

Not sure if anyone understands this, but it's driving me crazy. Thanks in advance.

Richard

 

[ryno525]

Can you post the formulas you are using? I have used indirect in the way you are, and I have used vlook up many times. It is not clear to me exactly what you are trying to do.

also Vlookup can be pretty picky.

 

[Gavona]

Also can you confirm that the problem occurs when the workbook you are retrieving the data from is open?


Gavin

 

[byundt]

Richard,
If the VLOOKUP is returning values, but they are incorrect, then perhaps you are not using FALSE as the fourth parameter. If the fourth parameter is either TRUE or omitted, then your lookup table must be sorted in ascending order by its first column. If it isn't sorted, then the VLOOKUP will find a "match" at an earlier row in the table.
=VLOOKUP(A1,Sheet2!A1:C500,3,FALSE) exact match for A1 required!
=VLOOKUP(A1,Sheet2!A1:C500,3) may return value from earlier row in table than exact match for A1
Brad

 

[dennycsr]

Make sure that the array where the vlookup is targeted is sorted. I have had a simular problem when the vlookup data was not sorted.

 

[rlee16]

thanks everyone especially to byundt,

As byundt mentioned, i messed up on the fourth parameter, I can't believe I missed it. Thanks!