[Solved] Lookup error with unsorted lists

[Dean Hawes]

Hello all.

I hate to ask this often repeated question as I know it has been addressed in many guises.
I have tried the Match/Index work around with no success, and many other ideas.

The attached cacl file has multiple attempts already in the cells.
We are looking at the 'Product Printer' Page with reference to Cells D9 & D18 for example.

Basically I am trying to extract data to this page so I can print out BOM's for each product.

The 'Lookup' formula works only for the first product due to 'SORTING' issues but as soon as I select any other product it goes pear shaped.

I appreciate any input on this one as always.

best wishes
Dean.H

[Villeroy]

A spreadsheet is not a database and all the lookup functions are designed for a different purpose. You must not use LOOKUP when misusing a spreadsheet as database. You've got to use VLOOKUP with zero as 4th argument.
Then you should turn off the regular expressions in Tools>Options>Calc>Calculation and turn on the "match whole cells" option.
[Tutorial] VLOOKUP questions and answers

[fst]

Hi,

this is an extract from the help file about LOOKUP:

Additionally, the search vector for the LOOKUP must be sorted ascending, otherwise the search will not return any usable results.

So your problem is just the unsorted search vector. Correct it and it will work.

Frank

[Villeroy]

Hi,

this is an extract from the help file about LOOKUP:

Additionally, the search vector for the LOOKUP must be sorted ascending, otherwise the search will not return any usable results.

So your problem is just the unsorted search vector. Correct it and it will work.

Frank

No, it won't work because it returns false positives for *any* value >= the first one.

[Dean Hawes]

Ok, trying that now.

Do you mean turn on : 'Search criteria = and < > must apply to whole cell'

Cheers

[Dean Hawes]

This list would be 2 complicated for user interpretation to have it sorted so its just not an option. But I have considered it.

[Dean Hawes]

I have tried the VLOOKUP formula with 0 as the 4th argument and get a #VALUE error.

I have posted the changed CALC file for inspection.

Dean

[Villeroy]

=VLOOKUP($D$5 ; $'INPUT PAGE'.$C$19:$AG$58 ; 31 ; 0)
looks up in C and returns the exactly matching value from the 31st column (AG) regardless of sort order.

[JohnSUN-Pensioner]

D5 =VLOOKUP($D$5;'INPUT PAGE'.$C$19:$AG$58;24;0)
D18 =VLOOKUP($D$5;'INPUT PAGE'.$C$19:$AG$58;31;0)

[Dean Hawes]

[SOLVED]

Nice one gents.

I am not sure how this formula works but it does work and I really appreciate the help.

Cheers again.
Dean

[RoryOF]

The $ references are dealt with in
[Tutorial] Absolute, relative and mixed references
and Vlookup in
[Tutorial] VLOOKUP questions and answers

It is worth reading up on them, and asking further questions for clarification.