Geek Answers Handbook

Sorting a table used by another Excel worksheet produces different VLOOKUP results

I am creating a toolbar in Excel 2010 that uses a function VLOOKUPto call other sheet values.

I am using the following equation:

= VLOOKUP (L $ 1, Sheet_B $ A: $ H, 7.2)

L$1requests a unique identifier on Sheet_A, and then finds that identifier on Sheet_B, and then finds the corresponding data and pulls it out. The problem is this:

If I do nothing for Sheet_B, I get the value - let's say 5. This value is incorrect.

If I sort Sheet_Bfrom AZ in alphabetical order (which does not change the data), I get a completely different value - let's say 12 is the correct value.

The problem is that the data that the function VLOOKUPchecks has not changed, just how it was sorted into Sheet_B.

Is there a reason why this could happen? And more importantly, how can I fix it, so I don’t need to sort it Sheet_Bevery time I pull out this panel?

+4
source share
1 answer

If your 4th parameter is equal True( 2in your case), VLOOKUPtrying to find an approximate match, so you need the values ​​in the first column to be table_arrayplaced in ascending order.

0 ( False), VLOOKUP , .

, VLOOKUP :

=VLOOKUP(L$1,Sheet_B!$A:$H,7,0)

Microsoft Office.

+4

More articles:


All Articles