Power Excel Techniques: 5/2/99 How to use INDEX(MATCH()) ins

Enjoy_Playing · Apr 22nd, 2002, 07:10 AM

I tried the =MATCH(A10,$B$2:$B$6,FALSE)and the =INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1) using your employee worksheet sample in the article, but the results showed "N/A" for both these statements. I've tried different values (A8 i/o A10), but I still get the same results.

I wanted to see if these functions would work for in getting data from different rows on Sheet1 (database) linked to Sheet2 (report), Sheet3 (report), Sheet4 (report), instead of having to copy/paste. So, if I have a "X" in column A1, a "Y" in column A2, and a "Z" in column A3, how do I tell excel to link all "X" rows in column A to Sheet2, all "Y" rows in column A to Sheet3 and all "Z" rows in column A to Sheet4? Will INDEX/MATCH work, or is there a better way to do this?

I sure would appreciate knowing why I couldn't get the above stated formulas to work, and if INDEX/MATCH is the solution for my linking data to different reports. Thank you in advance.

Rocky E · Apr 22nd, 2002, 07:50 AM

Hi JP,

Well I don't have a clue as to why your formulas don't work, but I can assure you that that they will. I usually open a new workbook, the help file and work the examples on a clean sheet until I get it down.

As to which on to use it just depends. Often more on how the data is organized than anything else. I my case each plant/department has a database and for some reports I just reference the individuals, but corporate wants an overview.

Most of the time if you are looking up something I use the Vlookup, Hlookup or Lookup, but if I just need to retrieve from a matrix and I know the offset I will use index.

If you can give a little more info, maybe, we can figure out your specific problem.

HTH

Rocky...

[ This Message was edited by: Rocky E on 2002-04-22 06:53 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 22nd, 2002, 07:10 AM	#1
Enjoy_Playing New Member Join Date: Apr 2002 Posts: 1	I tried the =MATCH(A10,$B$2:$B$6,FALSE)and the =INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1) using your employee worksheet sample in the article, but the results showed "N/A" for both these statements. I've tried different values (A8 i/o A10), but I still get the same results. I wanted to see if these functions would work for in getting data from different rows on Sheet1 (database) linked to Sheet2 (report), Sheet3 (report), Sheet4 (report), instead of having to copy/paste. So, if I have a "X" in column A1, a "Y" in column A2, and a "Z" in column A3, how do I tell excel to link all "X" rows in column A to Sheet2, all "Y" rows in column A to Sheet3 and all "Z" rows in column A to Sheet4? Will INDEX/MATCH work, or is there a better way to do this? I sure would appreciate knowing why I couldn't get the above stated formulas to work, and if INDEX/MATCH is the solution for my linking data to different reports. Thank you in advance.

Apr 22nd, 2002, 07:50 AM	#2
Rocky E Board Regular Join Date: Feb 2002 Location: North Alabama, USA Posts: 105	Hi JP, Well I don't have a clue as to why your formulas don't work, but I can assure you that that they will. I usually open a new workbook, the help file and work the examples on a clean sheet until I get it down. As to which on to use it just depends. Often more on how the data is organized than anything else. I my case each plant/department has a database and for some reports I just reference the individuals, but corporate wants an overview. Most of the time if you are looking up something I use the Vlookup, Hlookup or Lookup, but if I just need to retrieve from a matrix and I know the offset I will use index. If you can give a little more info, maybe, we can figure out your specific problem. HTH Rocky... [ This Message was edited by: Rocky E on 2002-04-22 06:53 ]