MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using Match and Index simultaneously...


Posted by Dennis Golbourne on December 28, 2000 11:16 AM

Hello,

I have seven sheets of Data, each sheet could range from 800 to 25,000 rows. I am trying to do an INDEX and MATCH on a value but currently, I am getting a{#N/A}...

The look-up value and the look-array are both formatted as 'TEXT' cell. Currently working on office 2000.

Any help will be appreciated.

Dennis


Posted by cpod on December 28, 2000 12:13 PM

Could you give us an example of a formula that is not working?

Posted by Dennis Golbourne on December 28, 2000 12:19 PM

I am sorry, here is the formula....
=INDEX(Global_00!A$8:AH$11249,MATCH($C$5,Global_00!A$8:A$11249,0),34)

Thanks again
Dennis

Posted by cpod on December 28, 2000 12:32 PM

Try setting both array addresses to absolute for both rows and columns.

Posted by Vijay on December 29, 2000 3:52 AM

Hi Dennis,
(1)Please check if MATCH($C$5,Global_00!A$8:A$11249,0)returns any error when you try this formula separately. If this is giving an error you will have
problems.
(2) What is the value in C5 is it Numerical or text. if Numerical Change the format of coloumn A in Global_00 sheet to ABS value. This should solve your problem

Regards
Vijay

Posted by Dennis G. on January 16, 2001 2:18 PM


Thank you all for the help...