VLOOKUP Trouble....

[BarryNeaves]

I am trying to create a formula in MS Excel 2000.

I am looking at a fixed value ab123456c in a cell, formatted as
text.

I want to look at a range of a different table, to respond that it has
found an exact match, and to look up a date next to the ab123456c and
return the date into the formula cell, or FALSE if there's no match.

I have used the VLOOKUP function, but it seems to return the closest
match and not an exact match, if the correct match is not available.

=VLOOKUP($F$2,'[differentbook.xls]sheet1'!$A$10:$F$403,2,TRUE)


Hopefully you'll be able to understand the above, it sounded better in
my head....

can anyone help correct this? or suggest an alternative?

Many thanks.

 

[Frank Kabel]

Hi
try
=IF(ISNA(VLOOKUP($F$2,'[differentbook.xls]sheet1'!$A$10:$F$403,2,false)
),false,VLOOKUP($F$2,'[differentbook.xls]sheet1'!$A$10:$F$403,2,false))

the 4th parameter has to be set to 'false' to get an exact match

 

[JudithJubilee]

You need to swap TRUE for FALSE in your formula. That
should sort it out.

Judith

 

[JudithJubilee]

The above will give you the error message #N/A if it
cannot find an exact match. If you want the word FALSE
to appear you need to put an If(ISNA( statement round it.

=IF(ISNA(VLOOKUP($F$2,'[differentbook.xls]sheet1'!
$A$10:$F$403,2,FALSE)),"FALSE",VLOOKUP
($F$2,'[differentbook.xls]sheet1'!$A$10:$F$403,2,FALSE))

Judith

 

[BarryNeaves]

I couldn't see the wood for the trees........

I have changed the formula t
=VLOOKUP($F$2,'[differentbook.xls]sheet1'!$A$10:$F$403,2,False)

and Bob's your aunty's live in lover!

Many thanks for all your contributions