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

yogi_Set Up Formula To Tabulate Number Of Correct Responses

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

user brainbrst said:
Arrayformula to tabulate number of correct reponses
I have a spreadsheet fed by a form with 20 multiple choice answers. In the last column of the row of each form submission I would like to tabulate the number of correct answers in relation to a second sheet from the same document. Is this possible to do with one formula or do I have to populate another sheet with a tally of each individual correct response and then count those?
so for example
response sheet Correct answer sheet (just 1 row or column with all correct answers. I would prefer column because there are other fields I'd like to include with the answers)
resp1| respr2| resp3|....|total correct aswr1 | aswr2| aswr3
  A  |   B   |   C  |       1 ........... A       C      B
. B  |   B   |   B  |       1
  A  |   B   |   B  |       2

---------------------------------------------
following is a solution to the problem

yogi_Set Up Leader Board For Top Scores -- MarinusP or AdamPL

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

In the following thread ...
http://yogi--anand-consulting.blogspot.com/2012/03/yogisum-up-amount-spent-by-category.html
there ensued an interesting discussion on the Leader Board for Top Scores -- the original poster Rhyno Max can settle the issue ... in the mean time we have two Leader Board scenarios:
1) MarinusP Leader Board
2) AdamPL Leader Board
the issue was which is the one to use. I thought why not set up Two Leader Boards, one proposed by MarinusP, and the Other by AdamPL
------------------------------------------
so without much further ado here we go:

yogi_Compare 2 Columns Of Names And Remove Names Listed In Column 2 From Column 1

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

user Stoked Charlie said:
Compare 2 columns of names and remove names listed in column 2 from column 1
I have a mailing list of e-mail addresses (column A) and another list of e-mail addresses (Column D) that need to be removed from the mailing list.
How can I have google docs, compare the lists and delete the matches?
If it is not possible to delete the matches, can google output a list of matches into another column, in order for me to delete them from the mailing list.
Any and all help is appreciated.
Thanks.
-----------------------------------------
following is a solution to the problem

yogi_Sum Up Amount Spent by Category

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

user biggiesmalls said:
Not Sure what function to use or how to set it up
A B C D E F
Date      Place        Amount  Sales Tax   Category 
2/22/2012 CCHMC         $6.24              food     
2/22/2012 Costco gas   $65.00              gas 
2/25/2012 Once Upon a  $63.36       3.87   well being
2/25/2012 Serenity      51                 well being 
2/25/2012 mcd            6.72              food
I am looking to sum the dollar amounts in Column C based on whether it is labeled as food ,gas, grocery etc in column E (Category). I want to track my expenses based on what type of expenditure it is. The total is to be reflected in column G. I am guessing from what I am reading that I need to use the sumif function entered as: SUMIF(E4:E11,food,C4:C11). This says error not sure what I am doing wrong. Can someone please help? Thanks.
----------------------------------------------
if you want to sum up only 1 item such as food use
=SUMIF(E4:E11,"food",C4:C11)
to get all the category items summed by have a look at the following solution to the problem

yogi_Sum Up An Item In A Column To Fulfill Multiple Criteria

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

user sjchow said:
How to sum total amount in 1 column which needs to fullfill 2 criteria in separate column?
Hi,
I have been trying to computer the total annual leave consumed by each employee.
Column C= Employee code (example: jeremy lee = jjlee)
Column D = no of days employee applied for. (Number is either 0.5 or 1 represent half or full day)
Column E= Type of Leave (AL= Annual leave...etc)
For example, how do I calculate the total annual leave taken by Jeremy?
I have tried : "=SUM(SUMIF(C:C='sjchow',{0.5,1},E:E="al"))" but it still prompt parse error.
I have tried filter function as well. It did successfully but I can't sum up the whole column amount.
Thanks!
------------------------------------------
following is a solution to the problem

yogi_Compute Entity Per Specification In Column(s) Row By Row

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

user celsius said:
arrayformula correct syntax?
=ArrayFormula(if(len(I2:I);(QUOTIENT(J2:J,3)*25)+(MOD(I2:I,3)*10);iferror(1/0)))
1. Is this correct syntax? including parentheses (It is not working).
2.I believe this is checking to see if there is a valid number value in I2 (& then proceed), but I need to check for a valid number value in both
I2 and J2.
4. Also, where do I place my title for the column as in something like
IF(ROW(A:A)=1;ʺPriceʺ;
Thank you again.
UPDATE
Thank you Yogi!
Your formula worked
=ArrayFormula(if(row(YY:YY)=1,"Price",if(len(I:I);(QUOTIENT(I:I,3)*25)+(MOD(I:I,3)*10);iferror(1/0))))
How do I add in a summing of the same algorithm for column J (in addition to column I).
Maybe - can one sum formulas, or do we another + with brackets?
=ArrayFormula(if(row(YY:YY)=1,"Price",if(len(I:I);(sum(QUOTIENT(I:I,3)*25)+(MOD(I:I,3)*10):(QUOTIENT(J:J,3)*25)+(MOD(J:J,3)*10));iferror(1/0))))
---------------------------------------------
following is a solution to the problem: