Can you check this formula

[LB_UK]

Thanks to those who offered advice on my original posting. I have, through trial and error come up with this formula which work well on my original spreadsheet but on subsequent sheets with very similar data the formula only worked in a few cells and I've no idea why. Any Ideas?

=INDEX(Sheet1!$A$1:$A$500,MATCH(A3,Sheet1!$H$1:$H$500,0),0)
The spreadsheet layout and data are as follows
the "order sheet" contains a code number (column A) along with other columns of data(quantities etc.)Sheet1 is the master list containing the code numbers(can be any column so the formula has to be changed for each sheet until I standardise it) and addresses etc plus an addition reference number (columnA) for each entry. I need to match the code numbers and put the reference code from Sheet1 into a column in "order sheet"
Thanks in advance

 

[Dave Hawley]

Hi

You have Zero as the Column_Num argument in the INDEX function, this is not possible! It must be no less than 1

 

[Aladin Akyurek]

Not sure I understand the layout. But, what do you get in those cells when your formula

=INDEX(Sheet1!$A$1:$A$500,MATCH(A3,Sheet1!$H$1:$H$500,0),0)

does not work?

Aladin

 

[Mark W.]

Your column_num argument (in<font color=red>red</font> below) isn't faulty... just unnecessary! When a 0 is used in this fashion it indicates that you're using the array form of the INDEX function and means "return all columns of the array". In this case there is only 1 column (A:A) in the array.

=INDEX(Sheet1!$A$1:$A$500,MATCH(A3,Sheet1!$H$1:$H$500,0),<font color=red>0</font>)
This message was edited by Mark W. on 2002-04-02 09:42

 

[LB_UK]

With the existing formula I get approx 2% of my list with correct information. all others have #N/A against them,

I've tried without the 0 (zero) but no success. I could email a copy of the spreadsheet if it would help