LOOKUP not working

[shaw600]

Hi,

I have a LOOKUP function on one sheet, it's aim is to look for a previous reference and then display the new reference from a database on a different sheet. However, it displays the wrong value for most of them. See the pictures below.

Database:

Formula:

Example (it should be BUILD-16):

I have tried using a VLOOKUP instead but that yielded similar results.

Please help.

 

[AliGW]

I don't see any 14 December entry in the top screenshot, so it's hard to judge. You need to make it clear what outcome you are expecting and why.

 

[arthurbr]

From MS:

The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

Try
=VLOOKUP(B8,CHOOSE({1,2},'Job Database'!E:E,'Job Database'!A:A),2,0)

 

[shaw600]

Thank you arthurbr, that works!!

May I ask how it works?

 

[BarryL]

I think thhe below explains it.

Excel VLOOKUP to the Left Using CHOOSE • My Online Training Hub

 

[arthurbr]

BTW an INDEX/MATCH combination is much more flexible