1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel: Lookup a corresponding value, multiple conditions

Discussion in 'Business Applications' started by impelus, Dec 22, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. impelus

    impelus Thread Starter

    Joined:
    Nov 28, 2005
    Messages:
    53
    Hi all, sorry for the vague title, but not really sure how to describe my problem other than to show it.

    Basically I am trying to do a VLOOKUP/HLOOKUP where one 1 value is exact and 1 value is approximate. I have tried the INDEX(MATCH method, but it doesn't work either since they need to be exact.

    I have publish the problem on google docs @ https://spreadsheets.google.com/ccc?key=tweLkcs0iso9ZF85qEexO4Q&hl=en#gid=0
    You will see lookup table on the left and then the right is a list of data that needs to find the answer to. I filled in the answers, just so you would see what I the correct answer should be. I absolutely need the lookup portion as I have way more than A and B.

    I am using Excel 2007. I would prefer a code I can write in the cell and not a macro/vba.

    Thanks in advance,

    Jason
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Is that GoogleDocs thing supposed to be editable? :confused:

    "I would prefer a code I can write in the cell and not a macro/vba."

    OK chief. :D

    In J3:

    =INDEX(INDIRECT("B"&MATCH(G3,A:A,0)+1&":E"&MATCH(G3,A:A,0)+1),1,MATCH(H3,INDIRECT("B"&MATCH(G3,A:A,0)&":E"&MATCH(G3,A:A,0)),1))

    :)eek:)
     
  3. impelus

    impelus Thread Starter

    Joined:
    Nov 28, 2005
    Messages:
    53
    I didn't make it editable since I didn't want someone to change something and then the others not see what I was asking. I made the worksheet editable now (one sheet is read only).

    OK. That works. Google Docs gives a wrong answer but excel does it correctly. It gives the answer for the one above.

    I replaced the "B" with G3 in your formula to make it dynamic.

    Unfortunately I have more than just 4 columns of data, but I can modify the formala to work with it.

    Thanks for the help.
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Sure it seems to be working. :)
     

    Attached Files:

  5. impelus

    impelus Thread Starter

    Joined:
    Nov 28, 2005
    Messages:
    53
    Why does this only work for A,B,C,D in columns A & G ?
     
  6. impelus

    impelus Thread Starter

    Joined:
    Nov 28, 2005
    Messages:
    53
    Yes it works, I had a space when I copied it. I noticed it and edited my reply.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/970038

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice