Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Vlookup...I think (Excel 97)

    I'm working with Excel 97 in Windows Me, but this application has to be usable in newer versions of Excel running under XP.

    I'm trying to get an Excel formula to work without using VBA code. I have a cell where the user can enter any 1 of 4 letters. The program needs to recognize each of these and look in a lookup table to get the answer. Right now I'm using Vlookup in an IF statement, but that can only find 2 of the letters. The code is:

    =IF(B16="S",(VLOOKUP(B4,range1,2,FALSE)),IF(B16="M FJ",(VLOOKUP(B4,range1,3,FALSE))))

    I know there has got to be a better way to do this, but I haven't programmed Excel in a few years and I have no help on this project. So any help will be greatly appreciated. I could probably do this with a select case structure in VBA, but I don't want to put macros into this spreadsheet.

    MerlynsDad
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup...I think (Excel 97)

    You could create another small table, and name it range2:

    <table border=1><td>S</td><td align=right>2</td><td>MFJ</td><td align=right>3</td><td>..</td><td align=right>..</td></table>
    You can use this for another lookup:

    =VLOOKUP(B4,range1,VLOOKUP(B16,range2,2,TRUE),FALS E)

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup...I think (Excel 97)

    Dad,
    Reading your description closely, it seems you want to look for answers for the four letters S, M, F, and J. If it's S, then the answer would be one answer, and if it's S, M or J, it's another (single?) answer. The attached spreadsheet shows a how two vlookups inside an IF statement might work using two answer columns, depending on whether the input is "S" (or "s") or the other letters.

    THere's the formula I used: =IF(UPPER(InputCell)="S",VLOOKUP(UPPER(C5),range2, 2,TRUE),VLOOKUP(UPPER(C5),range2,3,TRUE))

    (The UPPER business just makes sure "s"="S" to eliminate confusion.

Posting Permissions

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