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

Thread: lookup formula, 2nd try

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    I'll try and ask a different question....

    E.G.
    Can I return the value from A1 to cell G4, where C1 = F4...as below
    A B C D
    Chair Administration 7001 1
    Board Administration 7002 7001
    Chief Administration 7003 7001
    Manager Administration 7004 7003
    General Administration 7005 7001

    Col G returns......
    Chair
    Chair
    Chief
    Chair

    Many thanks

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    in cell g4
    =IF(C1=f4,a1,"")


    [ This Message was edited by: Qroozn on 2002-04-30 20:41 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to do more than a simple IF()....I need the formula to do this...

    =VLOOKUP(F3,C2:E19,-3)

    Obviously the -3 offset doesn't work and I ran into circular reference problems whe I tried to use a combination of OFFSET, ADDRESS, ROW and COLUMN....

    I'm sure it can be done somehow...

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-30 20:35, gideon wrote:
    Hi,

    I'll try and ask a different question....

    E.G.
    Can I return the value from A1 to cell G4, where C1 = F4...as below
    A B C D
    Chair Administration 7001 1
    Board Administration 7002 7001
    Chief Administration 7003 7001
    Manager Administration 7004 7003
    General Administration 7005 7001

    Col G returns......
    Chair
    Chair
    Chief
    Chair

    Many thanks
    I don't understand the context in which you are doing it or why you want to do it, however put the following formula in G1 and drag it down ...

    =IF($C$1=$F$4,$A$1,"it is not in the cards")

    If cell C1 and F4 both house 7001, then the formula written in cells G1:G6 will return

    Chair
    Chair
    Chair
    Chair
    Chair
    Chair

    Hope This Helps!


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    The reason you aren't getting responses is because you are confusing the hell out of us.
    if you are doing a lookup then the lookupvalue must be in the left most column.

    You cannot replace a value in a cell with another value based on a formula(without VBA)

    send the spreadsheet page to me with a description of exactly what you want..

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cheers,
    I am finding it hard to explain I must admit...maybe I'm asking too much of excel, I'll give it one more go.......

    Formula in G4
    1) lookup F4 in col E. this could be on any row of col E. e.g. E30
    2) return the value in col A on the same row of that found value e.g. A30.

    Hope this makes more sense.

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-30 20:55, gideon wrote:
    Cheers,
    I am finding it hard to explain I must admit...maybe I'm asking too much of excel, I'll give it one more go.......

    Formula in G4
    1) lookup F4 in col E. this could be on any row of col E. e.g. E30
    2) return the value in col A on the same row of that found value e.g. A30.

    Hope this makes more sense.
    Hi gideon:
    You are not asking of Excel too much ... you have to slow down and as our friend Qroozn said look at these things carefully and patiently.
    If I use the data that you originally posted with the scenario that you have just described, you don't need a VLOOKUP formula .. what you need is the following formula:

    =INDEX(A1:D6,MATCH(F4,C:C),1)

    Try to grasp what this formula is doing, play around with it with your own references and post back if it works for you ... otherwise explain a little further and let us takeit from there!


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks,

    I changed a few references and it works a treat..

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
  •