Look/Compare and return the Value

[DSP Varma]

I want to compare the values of Column C in Array (Column B) and return the figures of Column B in column D using a formula/function without disturbing the table.

 

[azumi]

That doesn't make sense, what the logic when you have value this 607 1609318798-799 and the result is 1411609318977?

 

[DSP Varma]

That doesn't make sense, what the logic when you have value this 607 1609318798-799 and the result is 1411609318977?

I am sorry. Since the data is manually entered I made a mistake. I am resending the file again.

 

[bosco_yip]

Try,

In E2, formula copy down :

=IFERROR(INDEX(B$2:B$43,MATCH(1,INDEX((LEFT(B$2:B$43,13)>=LEFT(SUBSTITUTE(C2," ",""),13))*(LEFT(B$2:B$43,13)<=LEFT(SUBSTITUTE(C2," ",""),10)&RIGHT(C2,3)),0),0)),"-")

Regards
Bosco

 

[DSP Varma]

Try,

In E2, formula copy down :

=IFERROR(INDEX(B$2:B$43,MATCH(1,INDEX((LEFT(B$2:B$43,13)>=LEFT(SUBSTITUTE(C2," ",""),13))*(LEFT(B$2:B$43,13)<=LEFT(SUBSTITUTE(C2," ",""),10)&RIGHT(C2,3)),0),0)),"-")

Regards
Bosco

Thanks Bosco. You did a wonderful job

 

[DSP Varma]

Can somebody get a formula for the above question as the above formula given by Bosco worked for me but it is taking lot of time and many times it is hanging my system.

Thanks in Advance

 

[Marc L]

Hi !

Very difficult as Excel is not a database software
and without a smart reorganized worksheet …

As a starter, a sample for D9 cell :

=VLOOKUP(SUBSTITUTE(LEFT(C9,SEARCH("-",C9)-1)&"*"," ",""),$B$2:$B$43,1,FALSE) …
​