Friday, March 9, 2012

yogi_Use VLOOKUP And HLOOKUP In The Same Formula

Yogi Anand, D.Eng, P.E.                Google Spreadsheet          Post  #465          www.energyefficientbuild.com

user PriestessMars said:
Can you use VLOOKUP and HLOOKUP in the same formula?
Is it possible to use VLOOKUP and HLOOKUP in the same formula? For example:
Sheet 1
A B C D
1 Name   Address          City         State
2 Sara   123 Main Street  New York     NY
3 Tom    345 Apple Place  Los Angeles  CA
4 George 567 Maple Lane   Boston       MA
4 Abby   789 Rigel Ave    New York     NY
Sheet 2
A B
1 Name State
2 Sara ???
3 George ???
I want B2 to come back with NY, B3 with MA. I know I can use the VLOOKUP function to get there, but if I have 20 different columns and only want the sheet to contain the information for some of it, is there a way I can have it lookup Sara & George where the column header is "State" (or some other text value)?
Thanks for the help and I'm sorry if this question has already been asked.
---------------------------------------------------
following is a solution to the problem