Lookup Values in any column

  • Hi, I have a table that I would like to perform a search in and then return the value one column over.


    For example, I have a cell (A1) with value: EL3, I want it to look up in the whole table (let's us call "Prices") for EL3 and then return the value 1 column over (220). However, A1 value can change to PL4, or HVAC 4. I want it search the whole table instead of using just Vlookup/Hlookup which is limited to one colum or row. So, the next time A1 has the value of HVAC4, it would search "Prices" and return 293.



    [TABLE="width: 418"]

    [tr]


    [td]

    EL1

    [/td]


    [TD="align: right"]73[/TD]

    [td]

    PL1

    [/td]


    [TD="align: right"]55[/TD]

    [td]

    HVAC1

    [/td]


    [TD="align: right"]73[/TD]

    [/tr]


    [tr]


    [td]

    EL2

    [/td]


    [TD="align: right"]146[/TD]

    [td]

    PL2

    [/td]


    [TD="align: right"]110[/TD]

    [td]

    HVAC2

    [/td]


    [TD="align: right"]146[/TD]

    [/tr]


    [tr]


    [td]

    EL3

    [/td]


    [TD="align: right"]220[/TD]

    [td]

    PL3

    [/td]


    [TD="align: right"]165[/TD]

    [td]

    HVAC3

    [/td]


    [TD="align: right"]220[/TD]

    [/tr]


    [tr]


    [td]

    EL4

    [/td]


    [TD="align: right"]293[/TD]

    [td]

    PL4

    [/td]


    [TD="align: right"]220[/TD]

    [td]

    HVAC4

    [/td]


    [TD="align: right"]293[/TD]

    [/tr]


    [tr]


    [td]

    EL5

    [/td]


    [TD="align: right"]366[/TD]

    [td]

    PL5

    [/td]


    [TD="align: right"]275[/TD]

    [td]

    HVAC5

    [/td]


    [TD="align: right"]366[/TD]

    [/tr]


    [tr]


    [td]

    EL6

    [/td]


    [TD="align: right"]439[/TD]

    [td]

    PL6

    [/td]


    [TD="align: right"]330[/TD]

    [td]

    HVAC6

    [/td]


    [TD="align: right"]439[/TD]

    [/tr]


    [tr]


    [td]

    EL7

    [/td]


    [TD="align: right"]512[/TD]

    [td]

    PL7

    [/td]


    [TD="align: right"]385[/TD]

    [td]

    HVAC7

    [/td]


    [TD="align: right"]512[/TD]

    [/tr]


    [tr]


    [td]

    EL8

    [/td]


    [TD="align: right"]585[/TD]

    [td]

    PL8

    [/td]


    [TD="align: right"]440[/TD]

    [td]

    HVAC8

    [/td]


    [TD="align: right"]585[/TD]

    [/tr]


    [tr]


    [td]

    EL9

    [/td]


    [TD="align: right"]659[/TD]

    [td]

    PL9

    [/td]


    [TD="align: right"]495[/TD]

    [td]

    HVAC9

    [/td]


    [TD="align: right"]659[/TD]

    [/tr]


    [tr]


    [td]

    EL10

    [/td]


    [TD="align: right"]732[/TD]

    [td]

    PL10

    [/td]


    [TD="align: right"]550[/TD]

    [td]

    HVAC10

    [/td]


    [TD="align: right"]732[/TD]

    [/tr]


    [/TABLE]



    I hope this is clear enough. Thank you for your help in advance.

  • Re: Lookup Values in any column


    Hi, my apologies. I can't seem to post the file with the forum's attachment function. I will try to do so when I get to a different computer.

  • Re: Lookup Values in any column


    You could name each 10x2 sections separately i.e. aa, ab, ac
    along with a couple of IFERROR formulas in your lookup:


    IFERROR(VLOOKUP($A1, aa, 2, FALSE), IFERROR(VLOOKUP($A1, ab, 2, FALSE), VLOOKUP($A1, ac, 2, FALSE)))

  • Re: Lookup Values in any column


    Edit Never mind. This won't work. There are duplicate numbers in Post #1, and if one of those numbers is entered in A1 it errors. :(


    This is one way to do it.


    This formula in B1

    Code
    =INDEX($A$2:$F$11,MATCH(1,MMULT(--(A1=$A$2:$F$11),{1;1;1;1;1;1}),0),MATCH(1,MMULT({1,1,1,1,1,1,1,1,1,1},--(A1=$A$2:$F$11)),0)+1)


    [TABLE="class: grid"]

    [tr]


    [td]

    Row\Col

    [/td]


    [td]


    A


    [/td]


    [td]


    B


    [/td]


    [td]


    C


    [/td]


    [td]


    D


    [/td]


    [td]


    E


    [/td]


    [td]


    F


    [/td]


    [/tr]


    [tr]


    [td]


    1


    [/td]


    [td]

    EL3

    [/td]


    [td]


    220


    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]


    2


    [/td]


    [td]

    EL1

    [/td]


    [td]


    73


    [/td]


    [td]

    PL1

    [/td]


    [td]


    55


    [/td]


    [td]

    HVAC1

    [/td]


    [td]


    73


    [/td]


    [/tr]


    [tr]


    [td]


    3


    [/td]


    [td]

    EL2

    [/td]


    [td]


    146


    [/td]


    [td]

    PL2

    [/td]


    [td]


    110


    [/td]


    [td]

    HVAC2

    [/td]


    [td]


    146


    [/td]


    [/tr]


    [tr]


    [td]


    4


    [/td]


    [td]

    EL3

    [/td]


    [td]


    220


    [/td]


    [td]

    PL3

    [/td]


    [td]


    165


    [/td]


    [td]

    HVAC3

    [/td]


    [td]


    220


    [/td]


    [/tr]


    [tr]


    [td]


    5


    [/td]


    [td]

    EL4

    [/td]


    [td]


    293


    [/td]


    [td]

    PL4

    [/td]


    [td]


    220


    [/td]


    [td]

    HVAC4

    [/td]


    [td]


    293


    [/td]


    [/tr]


    [tr]


    [td]


    6


    [/td]


    [td]

    EL5

    [/td]


    [td]


    366


    [/td]


    [td]

    PL5

    [/td]


    [td]


    275


    [/td]


    [td]

    HVAC5

    [/td]


    [td]


    366


    [/td]


    [/tr]


    [tr]


    [td]


    7


    [/td]


    [td]

    EL6

    [/td]


    [td]


    439


    [/td]


    [td]

    PL6

    [/td]


    [td]


    330


    [/td]


    [td]

    HVAC6

    [/td]


    [td]


    439


    [/td]


    [/tr]


    [tr]


    [td]


    8


    [/td]


    [td]

    EL7

    [/td]


    [td]


    512


    [/td]


    [td]

    PL7

    [/td]


    [td]


    385


    [/td]


    [td]

    HVAC7

    [/td]


    [td]


    512


    [/td]


    [/tr]


    [tr]


    [td]


    9


    [/td]


    [td]

    EL8

    [/td]


    [td]


    585


    [/td]


    [td]

    PL8

    [/td]


    [td]


    440


    [/td]


    [td]

    HVAC8

    [/td]


    [td]


    585


    [/td]


    [/tr]


    [tr]


    [td]


    10


    [/td]


    [td]

    EL9

    [/td]


    [td]


    659


    [/td]


    [td]

    PL9

    [/td]


    [td]


    495


    [/td]


    [td]

    HVAC9

    [/td]


    [td]


    659


    [/td]


    [/tr]


    [tr]


    [td]


    11


    [/td]


    [td]

    EL10

    [/td]


    [td]


    732


    [/td]


    [td]

    PL10

    [/td]


    [td]


    550


    [/td]


    [td]

    HVAC10

    [/td]


    [td]


    732


    [/td]


    [/tr]


    [/TABLE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!