VLOOKUP values not updating

Brass Contributor

I am having trouble with Excel 365 when using VLOOKUP in the following manner;

I have two iterations each not working properly.

Iteration 1: =IF(VLOOKUP(v2.2!$C$6,v2.2!$L$8:$II$51,24,FALSE)="","Rec'd","x")

The formula shown directly above this line is located within a sheet named "Opener" and referencing cells contained within the same workbook in a sheet named "v2.2". In theory when the values in the cells referenced in the formula above the VLOOKUP should pick up the changed value and react differently. If the value exists it in the referenced cell it should return "Rec'd". If the same cell is empty it should return "x" to the cell containing the VLOOKUP formula in sheet "Opener"...it does not.

 

Iteration 2: =IF(VLOOKUP(v2.2!$C$6,v2.2!$L$8:$II$51,34,FALSE)=0,"Rec'd","x")

This version is identical in function to the aforementioned formula with the only difference being the column location for the VLOOKUP to interact with. And the result is the same as in the other iteration.

 

I have this same formula running successfully in other iterations;

=IF(VLOOKUP(v2.2!$C$6,v2.2!$L$8:$II$51,20,FALSE)>0,"Rec'd","x")

=IF(VLOOKUP(v2.2!$C$6,v2.2!$L$8:$II$51,19,FALSE)="","Rec'd","x")

In both of these cases the cells whose values should be updated do get updated without issue.

 

Thanks for your help in advance!

15 Replies

@Douglas997t You write "If the value exists it in the referenced cell it should return "Rec'd". If the same cell is empty it should return "x" to the cell containing the VLOOKUP formula in sheet "Opener"...it does not."

 

But the formula suggests the opposite. If VLOOKUP finds a blank (or 0), it returns "Record" otherwise "x". 

 

If this makes no sense, perhaps you can share file (via Onedrive) demonstrating the issue.

Is there a way to post a screen shot of the effected areas. It would make more sense that way.
Is there a way to post a couple of screen shots to better illustrate the issue? I don't see an upload function in here.

@Douglas997t Press the camera icon in the editor when you write a response and follow the instructions to upload a picture (i.e. a screenshot).

Screenshot 2022-06-09 at 08.23.10.png

@Riny_van_Eekelen 

 

The 1st image reflects the result "x" which designates the cell the formula is looking in doesn't not have anything in it. The 2nd image is of the sheet that contains the cell where the formula is determining if there is a value in there or not. If it does it should return "Rec'd" (received) back in the "Opener" Sheet. The 3rd & 4th images are what happens when the cell in the sheet "v2.2" doesn't have a value in it and how the cell where the formula exists in sheet "Opener" reacts to the change in value.

 

As I had eluded to within my original post, I have two virtually identical formulas providing the same functionality in adjacent cells that work fine and these two that don't. I have tried all obvious substitution methods of changing operators within the formula but nothing enacts a change to a fully operating auto-update upon a change in value.

@Douglas997t The are no images! And "virtually identical formulas" are NOT identical and may behave differently in different circumstances.

I watched them upload...will try again
Uploaded again...
And, in this case the formulas ARE essentially the same. The only differences is in the column reference and in the case where the data field being looked at being either a text field or numeric and the formulas reflect those nuances...

@Douglas997t Still no images.

I couldn't begin to tell you why that is. I went into the window, dropped (4) images one at a time and hit "Done"...twice now. And they don't come through. No idea how to proceed at this point.

@Douglas997t I've sent you a direct message.

Got it. I just sent the (4) images as a reply to your last message prior to this one. Hopefully they came through all right.

Hello,
It seems that two columns needs to meet condition column24 should be blank another column34 should be 0 right,
Code Column24 Column34
101       ""                  0
102        1                  0
103         ""                1
For meeting two conditions needs to add AND in the formula
=IF(AND(VLOOKUP(D2,$A$2:$C$4,2,FALSE)="",VLOOKUP(D2,$A$2:$C$4,3,FALSE)=0),"Rec'd","X")
Just try with same format with your formulas,
IF(AND(VLOOKUP(v2.2!$C$6,v2.2!$L$8:$II$51,24,FALSE)="",VLOOKUP(v2.2!$C$6,v2.2!$L$8:$II$51,34,FALSE)=0),,"Rec'd","x")
I hope that this will solve your issue.
Thanks

Hello Sivakumarrj and thank you for reaching out on this issue. Actually the column 24 and 34 are separate and distinct formulas meant to be triggered by two separate and distinct events as opposed to both being included in the same string. Essentially, IF this "VLOOKUP" result is an empty cell return "x" (tells me I still need to ask for that item from the opposing side) and if the result of that VLOOKUP is a cell with a value the result should be "Rec'd" (tells me the opposing side already responded to my request and I no longer need to ask them for that item). If >"" then "Rec'd" if not then "x".

Funny thing is that I corrected O, P & Q =IF(VLOOKUP(v2.2!$C$6,v2.2!$L$8:$II$51,24,FALSE)>"","Rec'd","x"
but the same correction didn't hold true with R. Thus far R has been corrected by reversing the order of "Rec'd and "x"
=IF(VLOOKUP(v2.2!$C$6,v2.2!$L$8:$II$51,34,FALSE)>"","x","Rec'd")
For God knows what reason that is working like clockwork...strange!

Thanks again for your insights!