Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Can you check this formula

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

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



  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Your column_num argument (in red 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),0)

    [ This Message was edited by: Mark W. on 2002-04-02 09:42 ]

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •