BadFish523
Board Regular
- Joined
- Feb 15, 2018
- Messages
- 56
I have a formula in a Google sheet, it works great but the way I'm writing it I'll be here for years as I still need about 200 more IFs! So is there a shorter way of writing this?
=IF(A4=5191,QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='5191'")
,IF(A4=78523,QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='78523'")
,IF(A4="8A06903",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='8A06903'")
,IF(A4=979,QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='979'")
,IF(A4=988915,QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='988915'")
,IF(A4=99304672,QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='99304672'")
,IF(A4=998593,QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='998593'")
,IF(A4="C404R-72",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='C404R-72'")
,IF(A4="D-11749",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='D-11749'")
,IF(A4="MC001",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC001'")
,IF(A4="MC002",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC002'")
,IF(A4="MC003",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC003'")
,IF(A4="MC004",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC004'")
,IF(A4="MC005",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC005'")
,IF(A4="MC006",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC006'")
,IF(A4="MC007",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC007'")
,IF(A4="MC008",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC008'")
,IF(A4="MC009",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC009'")
,IF(A4="MC010",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC010'")
,IF(A4="MC011",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC011'")
,IF(A4="MC012",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC012'"))))))))))))))))))))))
=IF(A4=5191,QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='5191'")
,IF(A4=78523,QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='78523'")
,IF(A4="8A06903",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='8A06903'")
,IF(A4=979,QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='979'")
,IF(A4=988915,QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='988915'")
,IF(A4=99304672,QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='99304672'")
,IF(A4=998593,QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='998593'")
,IF(A4="C404R-72",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='C404R-72'")
,IF(A4="D-11749",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='D-11749'")
,IF(A4="MC001",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC001'")
,IF(A4="MC002",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC002'")
,IF(A4="MC003",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC003'")
,IF(A4="MC004",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC004'")
,IF(A4="MC005",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC005'")
,IF(A4="MC006",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC006'")
,IF(A4="MC007",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC007'")
,IF(A4="MC008",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC008'")
,IF(A4="MC009",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC009'")
,IF(A4="MC010",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC010'")
,IF(A4="MC011",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC011'")
,IF(A4="MC012",QUERY('Calibrated Gauges'!A2:Z,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A='MC012'"))))))))))))))))))))))