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]
PL1
[/td]
[TD="align: right"]55[/TD]
HVAC1
[/td]
[TD="align: right"]73[/TD]
EL2
[/td]
[TD="align: right"]146[/TD]
PL2
[/td]
[TD="align: right"]110[/TD]
HVAC2
[/td]
[TD="align: right"]146[/TD]
EL3
[/td]
[TD="align: right"]220[/TD]
PL3
[/td]
[TD="align: right"]165[/TD]
HVAC3
[/td]
[TD="align: right"]220[/TD]
EL4
[/td]
[TD="align: right"]293[/TD]
PL4
[/td]
[TD="align: right"]220[/TD]
HVAC4
[/td]
[TD="align: right"]293[/TD]
EL5
[/td]
[TD="align: right"]366[/TD]
PL5
[/td]
[TD="align: right"]275[/TD]
HVAC5
[/td]
[TD="align: right"]366[/TD]
EL6
[/td]
[TD="align: right"]439[/TD]
PL6
[/td]
[TD="align: right"]330[/TD]
HVAC6
[/td]
[TD="align: right"]439[/TD]
EL7
[/td]
[TD="align: right"]512[/TD]
PL7
[/td]
[TD="align: right"]385[/TD]
HVAC7
[/td]
[TD="align: right"]512[/TD]
EL8
[/td]
[TD="align: right"]585[/TD]
PL8
[/td]
[TD="align: right"]440[/TD]
HVAC8
[/td]
[TD="align: right"]585[/TD]
EL9
[/td]
[TD="align: right"]659[/TD]
PL9
[/td]
[TD="align: right"]495[/TD]
HVAC9
[/td]
[TD="align: right"]659[/TD]
EL10
[/td]
[TD="align: right"]732[/TD]
PL10
[/td]
[TD="align: right"]550[/TD]
HVAC10
[/td]
[TD="align: right"]732[/TD]
[/TABLE]
I hope this is clear enough. Thank you for your help in advance.