VLookup #VALUE! error help needed to resolve

  • Thread starter Thread starter Kristin
  • Start date Start date
K

Kristin

The following is the funcation I have:
=VLOOKUP(B10,'FA CC Summary Report 1141'!F$9:G$92,2,0)

I have all the columns formatted the same; as in the column that the
function is using to lookup is text and so is the column for this figure in
order to pull back the appropriate answer. I have keyed the data instead of
having links. I have replaced the final '0' with TRUE & FALSE then put it
back. I have formatted the columns for text and for numbers.

But I am getting the #VALUE! error in SOME of the cells NOT all of the
cells. I don't know what else to do.
 
Try this heavier duty index/match, normal ENTER will do:
=INDEX('FA CC Summary Report
1141'!G$9:G$92,MATCH(TRIM(B10)&"",INDEX(TRIM('FA CC Summary Report
1141'!F$9:F$92),),0))
Above should yield better results. Success? celebrate it, hit YES below
 
Check for residual/formula returned errors (#VALUE!) in your lookup data and
in your reference col F data. Clean these up and all should be well.
 
The formula is now working appropriately even though I don't understand why.
Thank you for that formula, it is a good one.

I had checked the formatting of all sections prior to sending this question
out and double checked them yesterday. Then I copied a cell below where the
#VALUE! error first occurred which worked for the new figure then I changed
it back to the figure I wanted to be looked up in the formula. Now all the
cells are working accurately.

Kristin
 
Back
Top