Vlookup not working properly

[Guest]

Hi I'm currently running office 2000 and trying to get vlookup to work
properly.

I have 10 tables which must be referenced from one formula.
The formula must pick which table to use (currently have that working)
the formula must pick which row and column to use (currently have that
working)

I know that that I just said the formula works and it does. However it only
works for numbers >= 76 and I need it to work for any numbers entered

Here is the exact formula being used:

=IF(ISERROR(VLOOKUP(C3,INDIRECT("Sheet"&B3&"!"&"A1:U151"),Data!E3,TRUE))=TRUE," ",VLOOKUP(C3,INDIRECT("Sheet"&B3&"!"&"A1:U151"),Data!E3,FALSE))

As I say this formula works just fine for me for numbers 76-150 (150 is the
highest number on the table) and returns the correct result. Numbers 1-75
however do not return anything and there is data in the table that should be
returned. (Note there are 151 rows in the tables on the other sheets)

The tables contain both numeric and alphanumeric data and where the formula
returns data both are returned fine.

One thing the formula does not do that I would like it to (though its not
neccessary) is in the event of no exact match returning the next largest
number that is smaller than the search value.

Hopefully this is a fixable problem.
If you need additonal info please just let me know.

Jaime

 

[Franz Verga]

Nel post *Jaime* ha scritto:

Hi I'm currently running office 2000 and trying to get vlookup to work
properly.

I have 10 tables which must be referenced from one formula.
The formula must pick which table to use (currently have that working)
the formula must pick which row and column to use (currently have that
working)

I know that that I just said the formula works and it does. However
it only
works for numbers >= 76 and I need it to work for any numbers entered

Here is the exact formula being used:

=IF(ISERROR(VLOOKUP(C3,INDIRECT("Sheet"&B3&"!"&"A1:U151"),Data!E3,TRUE))=TRUE,"
",VLOOKUP(C3,INDIRECT("Sheet"&B3&"!"&"A1:U151"),Data!E3,FALSE))

As I say this formula works just fine for me for numbers 76-150 (150
is the
highest number on the table) and returns the correct result. Numbers
1-75
however do not return anything and there is data in the table that
should be
returned. (Note there are 151 rows in the tables on the other sheets)

The tables contain both numeric and alphanumeric data and where the
formula
returns data both are returned fine.

One thing the formula does not do that I would like it to (though its
not
neccessary) is in the event of no exact match returning the next
largest
number that is smaller than the search value.

Hopefully this is a fixable problem.
If you need additonal info please just let me know.

Jaime

Hi Jaime,

Without your file in front of me, the only thing I can suggest to you is to
put TRUE or FALSE in the VLOOKUPs in the same manner: or 2 TRUE or 2
FALSE...


--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Ciao

Franz Verga from Italy

 

[Biff]

Hi!

You have conflicting range_lookup arguments:

Data!E3,TRUE))...............Data!E3,FALSE))


Try the formula like this:

=IF(ISNA(VLOOKUP(C3,INDIRECT("Sheet"&B3&"!A1:U151"),Data!E3)),"",VLOOKUP(C3,INDIRECT("Sheet"&B3&"!A1:U151"),Data!E3))

This requires the table_array be sorted ascending.

Biff

 

[Guest]

Thanks for the help. Unfortuantly my tables are in decending order though.
However with your help I found the problem. Formula is now

=IF(ISERROR(VLOOKUP(C3,INDIRECT("Sheet"&B3&"!A1:U151"),Data!E3,FALSE))=TRUE," ",VLOOKUP(C3,INDIRECT("Sheet"&B3&"!A1:U151"),Data!E3,FALSE))

(Putting both cases of FALSE to TRUE causes the formula to not return
anything.)

Interestlying enough with the formula I had before I got it to work when I
specified a range of A1:U301 don't know why this is.

Unfortunatly the formula I have now doesn't get the largest number that is
smaller than my search result. but that can wait untill I figure out what is
wrong or get all 10 tables switched to assending format.

Thanks for the great and quick help.

Jaime