Excel 2007 : vlookup not working in every cell

[destructor121]

Hello,

I have attached a spreadsheet I am working on and absolutely cannot figure out why I am only get some of the values from the column B lookups, but column C seems to pick up all values just fine. Any ideas would be appreciated. Thanks alot

[TMS]

You have duplicate dates in the columns. The VLOOKUP in the B column is returning the first entry for the date, 03/09/10, which happens to be blank, whereas, the VLOOKUP in column C is also returning the first entry for that date but it's not blank, it's 54.

Regards

[Paul]

Hi Destructor, welcome to the forum.

VLOOKUP finds the first match in a column, and then returns a value from another column to the right. There are duplicate dates in both columns BT and BV (the lookup columns), HOWEVER, every time there is a duplicate in BV, the data in BU is next to the FIRST instance of that date.

In column BT there are duplicate dates, but the data in BU is next to the second (or third, fourth, etc.) instance of that date in column BT.

If you can adjust your data in BU so that it aligns with the first instance of each date in BT, your VLOOKUP's will work fine. If you can't move the data like that, you would need to use a more complex formula to retrieve the last value found for that matching date.

[JBeaucaire]

Your column BT dates have duplicates. The VLOOKUP only finds the first row and returns the value in BU in the adjacent cell, but all your deposits are in the second entry if more than one exists.

Perhaps this in B6, copied down:

=IF(ISNUMBER(MATCH(A6,BT:BT, 0)), INDEX(BU:BU, MATCH(A6,BT:BT, 0) + COUNTIF(BT:BT,A6)-1), "")

[Paul]

Another formula option for Excel 2007+ (this one is an array):

=IFERROR(INDEX(BU$2:BU$1000,MATCH(2,1/(BT$2:BT$1000=A6),1)),"")

This must be confirmed with CTRL+SHIFT+ENTER, not just ENTER. If you use BU:BU and BT:BT it will slow down your calculation time since it's looking at the whole column and not a defined range. If you're never going to have data below row 200, set that as your max.

[destructor121]

I appreciate the replies. I used Paul's formula in the TE section of my sheet and it worked perfectly. =IFERROR(INDEX(BU$2:BU$1000,MATCH(2,1/(BT$2:BT$1000=A6),1)),"")

I guess I don't completely understand the Index Match formulas because I can't get it to work in the rest of the sections.

[Paul]

INDEX ( return_range , position_in_range )
MATCH ( lookup_value , lookup_array_or_range , type )

In an INDEX/MATCH function, the MATCH portion is returning the position_in_range to the INDEX function. It becomes:

INDEX ( return_range , MATCH ( lookup_value , lookup_array_or_range , type ) )

In your case, you want to return values from column BU (BU2:BU1000), so that becomes the "return_range".

In the MATCH function you would normally provide a lookup_value (or cell reference containing that value), a column to search and a match type (-1, 0 or 1).

Match type 0 looks for an exact match. Type 1 looks for an approximate match in a list that is sorted in ascending order. It will return the largest value less than or equal to the lookup value. Type -1 looks for an approximate match in a list that is sorted in descending order. It will return the smallest value greater than or equal to the lookup value. See Excel help for examples.

In this case, rather than searching a range of values for a specific value, I'm doing an approximate match and searching an array of values for 2. The array is created in the "1/(BT2:BT1000=A6)" part of the formula when you press CTRL+SHIFT+ENTER (array-entering a formula). This results in an array like "1/(TRUE;TRUE;FALSE;FALSE;FALSE)" which in turn becomes "1;1;DIV/0;DIV/0;DIV/0" because TRUE=1 and FALSE=0. 1 divided by 1 = 1 while 1 divided by 0 = DIV/0 error.

Since I'm trying to match 2 to an array of 1's and errors, the MATCH function will return the position of the last entry in the array that is less than or equal to 2 - in other words, the last instance of 1.

It's always tough to explain these things in words. If you select the cell and then Evaluate Formula (Formulas tab, Formula Audition section, Evaluate Formula) you will see each step as you walk through it.

[TMS]

@Paul: am I right in thinking that this will then not be useful in the column C VLOOKUP scenario? It is specifically to return the last value in the relevant range? Hence, in column C it will return a blank for 03/09/10

Regards

[Paul]

Hence why trying to say it in words is not always very good..

It will still work.. it actually returns the position of the greatest value equal to or less than the lookup value. In the case of multiple instances of that "greatest value" (1's in this scenario), it will return the last instance of it whether it's next to the first, second, tenth or whatever multiple of that data entry.

[TMS]

@Paul: OK, so, yes, it "works". But in this case, the transcribed formula:

=IFERROR(INDEX(BW$2:BW$1000,MATCH(2,1/(BV$2:BV$1000=$A6),1)),"") CSE'd,

returns the second occurrence of the date, 03/09/2010, but that has the blank value next to it.

I guess that's why the OP feels he is struggling to make the formula work in other columns.

Or am I missing something?

BTW: the original explanation was brilliant ... I think I understand it ;-) What I don't understand is, how people come up with these formulae in the first place!

[destructor121]

I dont know either my brain is boggled. lol When I use the formula in column C it does what the vlookup did with column B, doesnt catch all of the values. I am not sure I can do what I am wanting to do. Especially since any of the 5 modules could have values in any combination. Ive learned alot of excel in the past couple of weeks and still learning these higher level formulas and such.

You guys have been a big help and now Im closer to victory.

[Paul]

Try this one instead. It adds a test to see if the results column is blank.

=IFERROR(INDEX(BU$2:BU$1000,MATCH(2,1/((BT$2:BT$1000=A6)*(BU$2:BU$1000<>"")),1)),"")

[destructor121]

Sorry for the delay. That last formula worked! YAY lol. Thanks alot for all of your help. Im marking this solved.