Excel Formula Question - Fetching Data

[Kg Teh]

Dear all,

I need to fetch the data from a Table to Reporting Sheet. The arrangement of data may change from time to time. Could you all provide me some ideas what formula i should use to fetch the data ?

I have attached the sample file for your kind references.

Many thanks !!

 

[Asheesh]

Hi Kg Teh -

See the attached...I have used couple of helper rows(rows in grey) to keep it simple..

 

[NARAYANK991]

Hi ,

An alternative.

Narayan

 

[Kg Teh]

Hi Asheesh and Narayank991,

You guys are awesome! Thank you very much !

 

[Somendra Misra]

Hi,

Just another non array alternative.(Orange Cells)

Regards,

 

[Kg Teh]

Wow, there is another alternative. Thank you Somendra.

I would like to ask what is the pro and con using array formula in the worksheets?

 

[Kg Teh]

Hello Somedra, what is the meaning of 9.99999E+307 ?

 

[Somendra Misra]

@Kg Teh

If the data that you are handling is not large than array or non-array does not make any difference. But if you have large numbers of data than they do make a difference.

9.9999999E+307 is the biggest positive number Excel can handle. When used in LOOKUP function with dynamic range in lookup vector it return the last cell value.

Regards,

 

[NARAYANK991]

Hi ,

You might find the explanation given in this thread helpful :

http://chandoo.org/forum/threads/difference-in-lookup.19838/#post-119607

Please note that there is nothing sacrosanct about the fanciful number 9.9999E+307 ; depending on your data even a number like 9 might do.

Narayan

 

[Kg Teh]

Thank you somendra and narayank991. I have learnt something new today.