Vlookup another reference/answer if the first cell is blank

[HHHHH]

Hi

I am trying to get a Vlookup to give me the answer from another cell if the cell that I have looked up to is blank. (It is a spreadsheet from work and I cannot change the layout). I have tried to simplify what I am doing using the example in the picture below.


This is what I have written and it works only if column D is blank. It fails for all of the cells which have something in column D and for those ones it gives me the answer in column B, even though the latest update is in column D.

Does anybody know why?

Please note: the "" are coming out slanted, but they are normal in Excel and I split the formula up to make it easy to see).


=iferror(

if(vlookup(a2,’sheet2’!,$A:$D,4,false)&””=””,

If(vlookup(a2,’sheet2’!,$A:$D,3,false)&””=””,

(vlookup(a2,’sheet2’!,$A:$D,2,false)&””=””,

(vlookup(a2,’sheet2’!,$A:$D,4,false)),

(vlookup(a2,’sheet2’!,$A:$D,3,false)),

(vlookup(a2,’sheet2’!,$A:$D,2,false)),

“”)

All help will be very much appreciated.

 

[steve the fish]

Try this:

=LOOKUP("zzz",INDEX(sheet2!$B$2:$D$6,MATCH(A2,sheet2!$A$2:$A$6,0),0))

 

[HHHHH]

Try this:

=LOOKUP("zzz",INDEX(sheet2!$B$2:$D$6,MATCH(A2,sheet2!$A$2:$A$6,0),0))

Steve that works on my example I am going to try and apply that to my complicated work spreadsheet now. Finger crossed
THANK YOU.

 

[HHHHH]

Try this:

=LOOKUP("zzz",INDEX(sheet2!$B$2:$D$6,MATCH(A2,sheet2!$A$2:$A$6,0),0))

I managed to apply it correctly to my Excel workbooks in work. Thank you so much! This is going to save so much time and I have learnt another formula .