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
=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