Lookup value in two column

[JAMIR]

Respected Ninjas & Friends

I have attached sample file. In Vehicle Register there is two column FC Date & New FC Date. In RC Register i want FC Date from Vehicle Register if New FC Date column is Blank it should get FC Date Column Value. And if Both Column [FC Date & New FC Date] having dates it should be get New FC Date.

Regards,


Zameer

 

[JAMIR]

Respected Ninjas & Friends

I have attached sample file. In Vehicle Register there is two column FC Date & New FC Date. In RC Register i want FC Date from Vehicle Register if New FC Date column is Blank it should get FC Date Column Value. And if Both Column [FC Date & New FC Date] having dates it should be get New FC Date.

Regards,


Zameer

 

[Nebu]

Hi:

May be something like this.

Thanks

 

[Naresh Yadav]

Dear jamir,

pls try this one if it works for you..

 

[Naresh Yadav]

Dear Jamir,

pls try this one and finish it with CTRL+SHIFT+ENTER

=MAX(VLOOKUP([@[Vehicle No.]],Table1[[Vehicle No.]:[New FC Date]],{3,4},0))

 

[JAMIR]

Hi:

May be something like this.

Thanks

Hello Nebu Sir,

Thanks for help. Its works perfectly.

Have a great day

Regards,

Zameer

 

[JAMIR]

Dear Jamir,

pls try this one and finish it with CTRL+SHIFT+ENTER

=MAX(VLOOKUP([@[Vehicle No.]],Table1[[Vehicle No.]:[New FC Date]],{3,4},0))

Hello Naresh,

Thanks for help. Its very useful.

But i think instead of vlookup, index function is more reliable. Coz column reference may be change sometimes. And Index doesn't affect if column changed.

Again thanks for the great support.. Take care. have a great day.


Zameer

 

[bosco_yip]

Or this non-array formula and with error control :

=IFERROR(1/(1/(MAX(VLOOKUP(B2,Table1[[Vehicle No.]:[New FC Date]],{3,4},0)))),"")

or,

=IFERROR(1/(1/(MAX(INDEX(Table1[[FC Date]:[New FC Date]],MATCH(B2,Table1[Vehicle No.],0),)))),"")

Regards
Bosco

 

[JAMIR]

Or this non-array formula and with error control :

=IFERROR(1/(1/(MAX(VLOOKUP(B2,Table1[[Vehicle No.]:[New FC Date]],{3,4},0)))),"")

or,

=IFERROR(1/(1/(MAX(INDEX(Table1[[FC Date]:[New FC Date]],MATCH(B2,Table1[Vehicle No.],0),)))),"")

Regards
Bosco

Hello Bosco

Thank you very much..

Have great day.

Regards,


Zameer