Below is my excel function input but it does not seem to be working as it just returns -- in google docs.
=IF(ISERROR(VLOOKUP(F11,Formulas!$A2:$B17, 2, FALSE)),
VLOOKUP(F11,Formulas!$A2:$B17, 2, FALSE),
VLOOKUP(F11,Formulas!C2:D17, 2, FALSE))
Below is the information I am wanting to do a vlookup for.
A B C D
1 NYJ 27 PHI 20
2 BUF 13 DET 35
3 CIN 27 IND 10
4 MIA 24 NO 21
5 TB 12 WAS 30
6 CAR 25 PIT 10
7 ATL 16 JAC 20
8 NE 28 NYG 20
9 MIN 24 TEN 23
10 STL 24 BAL 21
11 CHI 16 CLE 18
12 KC 30 GB 8
13 DAL 6 HOU 24
14 DEN 24 ARI 32
15 SD 6 SF 41
16 SEA 22 OAK 6
Let's say a user inputs "GB" into cell F11. Point of the formula is to insert the points to the right of the GB. Formula is supposed to look for GB in the first column. IF it is TRUE, it will do a vlookup for the first column because it found GB in the first column. IF it is FALSE, it will do a vlookup for the 3rd column.
Not sure what is happening? FYI I did replace the last TWO vlookup functions in the formula with "TRUE" and "FALSE" and it worked as it should. Is this just a google docs issue?
Any help would be great! thanks
UPDATE
I tried this in excel and got a #N/A
3 Answers
As you are returning numbers an alternative here might be to use SUMIF, i.e.
=SUMIF(Formulas!A:C,F11,Formulas!B:D)
If F11 isn't found you'll get zero
Use IFERROR instead of ISERROR:
=IFERROR(VLOOKUP(F11,Formulas!$A2:$B17, 2, FALSE),VLOOKUP(F11,Formulas!C2:D17, 2, FALSE))
Your formula will always return #N/A with your current data. Your VLOOKUPs are good, but the issue is with the IF. Let me explain:
Let's call VLOOKUP(F11,Formulas!$A2:$B17, 2, FALSE) the function F1 and VLOOKUP(F11,Formulas!C2:D17, 2, FALSE) F2.
Your IF formula is:
=IF(ISERROR(F1),F1,F2)
Literally meaning: If F1 returns an error, use F1! If F1 does not return an error, use F2 (which by the way would now return an error).
Does this make sense? Try turning it around:
=IF(ISERROR(VLOOKUP(F11,Formulas!$A2:$B17, 2, FALSE)),
VLOOKUP(F11,Formulas!C2:D17, 2, FALSE),
VLOOKUP(F11,Formulas!$A2:$B17, 2, FALSE))
That should work now. You can also shortern it by using an IFERROR because you are repeating two operations:
=IFERROR(VLOOKUP(F11,Formulas!$A2:$B17, 2, FALSE),VLOOKUP(F11,Formulas!C2:D17, 2, FALSE))
Now it means if F1 gives an error, use F2, otherwise, use F1 itself.
