Sunday, September 30, 2012

yogi_Lookup Monthly Number For An ID From A Table Of Multiple Sets Of IDs And Monthly Numbers

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #787  Sep 30, 2012    www.energyefficientbuild.com.

user beejizzle said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/dYMOKdpPGVE)
Can I use vlookup to search in more columns than the left only?
I use =vlookup(A1; A2:I10;3; 0) right now. The table A2-J10 look liks this:
A1 = ID_to_search_for

ID | Jan | Price | ID | Feb | Price | ID | Mar | Price
10 | 11 | 1000 | 15 | 14 | 1400 | 9 | 8 | 100
99 | 11 | 90 | 77 | 14 | 200 | 21 | 8 | 400
44 | 11 | 900 | 12 | 14 | 1400 | 64 | 8 | 220

Vlookup can only search in the first, left, column. If the A1 is 9 I want to match with G2 and have the value from I2 Is there any formula or if statement that can make it search in all 3 ID fields?
Thanks
-------------------------------------------------------------------------------------------------------- 
following is a convoluted solution using the VLOOKUP function