Vlookup and sort

**exceldiehardfan** · 05-05-2010, 12:09 PM

I have a worksheet that lists job numbers acting sort of as primary key. I use several if statements and vlookups to get other info pertaining to that job from another sheet.

My problem is, one group prefers the jobs to be arranged by job number. Another group prefers the order to be based on possession date of the job (which is manually entered). I have the jobs arranged by job number as it's easier for me that way.

When I try to sort the list based on the possession date, the vlookup nested within if statements stop working. the lookup reference doesn't change accordingly to the row where the job falls into.

Any ideas?

**6StringJazzer** · 05-05-2010, 01:18 PM

It's hard to say for sure without seeing the sheet, and without a description of what is the incorrect behavior you see. But it sounds like you are using VLOOKUP with three arguments, which requires the data to be sorted (by job number, in your particular case). If so, add FALSE as a fourth argument, which requires an exact match regardless of how the data is sorted.

**exceldiehardfan** · 05-05-2010, 02:02 PM

Thanks for the reply and I apologize for not being specific.

Here's an example of my formula:
=IF(ISNA(VLOOKUP(E184,'sales price'!$A:$D,3,FALSE)),AI184,VLOOKUP('WIP Bank Detail'!E184,'sales price'!$A:$D,3,FALSE))

I am using false as a 4th argument because to make sure I get the exact match.

However, after I do an ascending sort based on a date column, the formula becomes:
=IF(ISNA(VLOOKUP(E197,'sales price'!$A:$D,3,FALSE)),AI197,VLOOKUP('WIP Bank Detail'!E203,'sales price'!$A:$D,3,FALSE))

Note that the lookup reference only changes at the first vlookup but the next vlookup doesn't seem to work.

**6StringJazzer** · 05-06-2010, 08:51 PM

I can't tell from what you posted what the problem is. Can you attach the workbook? I would want to see where the data is, how it's being sorted. Be sure and include details about how you sort. Do you sort a whole worksheet, or only certain columns?