+ Reply to Thread
Results 1 to 5 of 5

how to determine column heading from lookup

  1. #1
    Paul
    Guest

    how to determine column heading from lookup

    Say I have a table for phone commission

    ---------------------------
    Handset - TariffA - TariffB
    ---------------------------
    Nokia1 - =A350 - =A3100
    Nokia2 - =A360 - =A3200
    Sony1 - =A360 - =A3300
    ---------------------------

    and on another sheet I have

    ---------------------------
    Handset - Expected - Actual
    ---------------------------
    Sony1 - =A3300 - x
    ---------------------------

    I know I have a Sony1, and that I have =A3300 - how do I return
    "TariffB" as the cell value, X?

    Any ideas on how to do this?


  2. #2
    Tom Ogilvy
    Guest

    Re: how to determine column heading from lookup

    =if(B2=vlookup(A2,Table,2,false),"TariffA","TariffB")

    --
    Regards,
    Tom Ogilvy


    "Paul" <luap.h@totalise.co.uk> wrote in message
    news:1121956950.023492.259580@g49g2000cwa.googlegroups.com...
    Say I have a table for phone commission

    ---------------------------
    Handset - TariffA - TariffB
    ---------------------------
    Nokia1 - £50 - £100
    Nokia2 - £60 - £200
    Sony1 - £60 - £300
    ---------------------------

    and on another sheet I have

    ---------------------------
    Handset - Expected - Actual
    ---------------------------
    Sony1 - £300 - x
    ---------------------------

    I know I have a Sony1, and that I have £300 - how do I return
    "TariffB" as the cell value, X?

    Any ideas on how to do this?



  3. #3
    Paul
    Guest

    Re: how to determine column heading from lookup

    Thanks Tom,

    sorry, but that's not what I'm looking for, I should have explained
    more clearly.

    The table above will go from TariffA to Tariff X, and the same for the
    phone models, say around 40

    Maybe a macro would be better suited to my needs?

    Get the phone model and money in
    Search the table for the model
    Use the row number to look for the money in
    return the column number and use that to find the column title


  4. #4
    Tom Ogilvy
    Guest

    Re: how to determine column heading from lookup

    Phones listed in A2:A41
    Tariff names in B1:Y1
    Tarriff amounts in the body of the table. B2:Y41
    A1 is the upper left corner of the table above the phone list and to the
    left of the tariff list.
    In AB2 I enter the name of the phone of interest and in AC2 I enter the
    money amount. This formula returns the Tariff name.

    =OFFSET(A1,0,MATCH(AC2,OFFSET($A$1,MATCH(AB2,$A$2:$A$41,0),1,1,23),0))

    You should be able to adapt this approach to address your specific
    situation.

    --
    Regards,
    Tom Ogilvy


    "Paul" <luap.h@totalise.co.uk> wrote in message
    news:1122028249.885643.249100@g43g2000cwa.googlegroups.com...
    > Thanks Tom,
    >
    > sorry, but that's not what I'm looking for, I should have explained
    > more clearly.
    >
    > The table above will go from TariffA to Tariff X, and the same for the
    > phone models, say around 40
    >
    > Maybe a macro would be better suited to my needs?
    >
    > Get the phone model and money in
    > Search the table for the model
    > Use the row number to look for the money in
    > return the column number and use that to find the column title
    >




  5. #5
    Paul
    Guest

    Re: how to determine column heading from lookup

    Cheers Tom,

    that's awesome stuff - working a treat! Gonna sit with the help files
    and see if I can figure out how it works!


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1