Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: 2 Criteria Lookup Without #N/A

  1. #1
    Join Date
    31st March 2008
    Posts
    5

    2 Criteria Lookup Without #N/A

    I am trying to lookup the value in a table based on two criteria. For the purpose of illustration, I attached the file here.
    What I am trying to do is that I want to fill Table 2 with data from Table 1 which match the company and the dates. If all the dates match for the companies, that problem would be much easier, However, as you can see the dates from each company do not match on the same row,totally massed things up.

    In my real file I have 70 companies, any one have a solution for this ???It would be really appreciated for your help.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    21st February 2006
    Location
    London, UK
    Posts
    3,831

    Re: Find Value And Return Row Number

    Are you asking for a way to hide or remove the N/A error?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Find Value And Return Row Number

    Quote Originally Posted by StephenR
    Are you asking for a way to hide or remove the N/A error?
    If so, try,

    B17 and copied down & across,

    =IF(ISNUMBER(MATCH(B$16,INDEX($A$2:$F$13,,ROWS($B$17:$B17)*2-2+1),0)),HLOOKUP($A17,$A$1:$F$13,MATCH(B$16,INDEX($A$2:$F$13,,ROWS($B$17:$B17)*2-2+1),0)+1,0),"")

    HTH

  4. #4
    Join Date
    31st March 2008
    Posts
    5

    Re: Find Value And Return Row Number

    not exactly. Sorry for unclear statement of my question.

    The attachted file is justs a simple example of my database, In my real database I have 70 companies, and the data will be updated from time to time from Bloomberg.

    So I am looking for other way to retrive the right data, rather than what I did in the file by selecting the colums one-by-one in the Match() function for each company to find the row number.

    But also thank you to tell me the way to hide the error message, useful too!

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Find Value And Return Row Number

    rather than what I did in the file by selecting the colums one-by-one in the Match() function for each company to find the row number.
    If each company has two column data then try my formula. You don't need to select each column for each company.

  6. #6
    Join Date
    31st March 2008
    Posts
    5

    Re: Find Value And Return Row Number

    thanks for your quick reply Kris, I have applied your formula in Cell B 17, no value appears at all.can you work on the file and upload to the forum please?Don't know what went wrong.

    Thanks

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    23rd October 2003
    Location
    Alsace France
    Posts
    4,099

    Re: Find Value And Return Row Number

    I have been using Krish's formula and it works very nicely, see attached.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Triumph without peril brings no glory: Just try

  8. #8
    Join Date
    31st March 2008
    Posts
    5

    Re: Find Value And Return Row Number

    Indeed, it works!!!!!!!!!!

    Sorry, earlier I was at work, we use European standard format at work, so the formula didn't work.

    Thanks a lot Kris.
    I am gonna try apply it to my data sheet.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Find Value And Return Row Number

    Quote Originally Posted by linoutandabout
    Indeed, it works!!!!!!!!!!

    Sorry, earlier I was at work, we use European standard format at work, so the formula didn't work.

    Thanks a lot Kris.
    I am gonna try apply it to my data sheet.

    You are welcome!!

  10. #10
    Join Date
    31st March 2008
    Posts
    5

    Re: 2 Criteria Lookup Without #N/A

    It totally works in my database, you made my day Kris.

    zillions of thanks :D

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Lookup A Value With Three Criteria
    By dcgrove in forum Excel General
    Replies: 2
    Last Post: May 26th, 2008, 18:04
  2. 2 Criteria Lookup
    By Pulser in forum Excel General
    Replies: 2
    Last Post: February 16th, 2008, 15:48
  3. Lookup With More Than One Criteria
    By n0ra in forum Excel General
    Replies: 3
    Last Post: November 23rd, 2007, 13:32
  4. Two Lookup Criteria
    By prabal in forum Excel General
    Replies: 3
    Last Post: October 21st, 2006, 12:39
  5. Lookup using 3 criteria
    By Rosco in forum Excel General
    Replies: 4
    Last Post: February 22nd, 2006, 04:32

Bookmarks

Posting Permissions

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