VLookup #VALUE! error help needed to resolve

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.
0
Utf
2/1/2010 9:58:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
1235 Views

Similar Articles

[PageSpeed] 28

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
-- 
Max
Singapore
--- 
"Kristin" wrote:
> 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.
0
Utf
2/1/2010 10:47:01 PM
Still getting #VALUE! error.  Anything else you can think of?

Kristin

"Max" wrote:

> 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
> -- 
> Max
> Singapore
> --- 
> "Kristin" wrote:
> > 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.
0
Utf
2/2/2010 5:41:01 PM
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.
-- 
Max
Singapore
--- 
"Kristin" wrote:
> Still getting #VALUE! error.  Anything else you can think of?

0
Utf
2/2/2010 11:13:01 PM
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
"Max" wrote:

> 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.
> -- 
> Max
> Singapore
> --- 
> "Kristin" wrote:
> > Still getting #VALUE! error.  Anything else you can think of?
> 
0
Utf
2/3/2010 7:50:01 PM
Reply:

Similar Artilces: