need help in LOOKUP

[Mehmud Khan]

Hi,

Could anyone please find the attached excel file and provide me the solution with regards to LOOKUP functions.

I have written the query in the sheet only.

Thanks in advance.

Thanks and Best Regards,
Mehmud

 

[AliGW]

There are a lot of hidden columns. Please unhide the relevant columns. At the moment, your required steps make no sense at all.

 

[bosco_yip]

Maybe,

In M56, copied down :

=IFERROR(LOOKUP(9.9E+307,SEARCH(xMasterData!AE$5:AE$47,'Input Data'!AJ5),xMasterData!AF$5:AF$47),Q56)

Regards
Bosco

 

[Mehmud Khan]

Thanks for your quick reply bosco.

But I am confused with 9.9E+307 in the formula you have taken.

=IFERROR(LOOKUP(9.9E+307,SEARCH(xMasterData!AE$5:AE$47,'Input Data'!AJ5),xMasterData!AF$5:AF$47),Q56)

Could you please help in in that...

Regards,
Mehmud

 

[bosco_yip]

9.9E+307 is called "Bignum" in the Lookup formula (but in a lazy form)

In the ‘Excel calculation specifications and limits’ it says that the largest allowed positive number : 9.99999999999999E+307

Try to Google in search for "Bignum" for further detailings.

Regards
Bosco