Converting a LOOKUP formula

[headspinning]

I'm not sure if anyone can answer this question....

I am trying to put a spreadsheet into a google doc that I made in Excel. One of the cells has the following formula in excel

<style> <!-- /* Font Definitions */ @font-face {font-family:Cambria; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Cambria; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> =IF(L3=1,LOOKUP(C3,{1.0,2.0,3.0},{2.8,2.25,1.87}),IF(L3=2,LOOKUP(C3,{1.0,2.0,3.0},{3.8,3.2,2.67}),IF(L3=3,LOOKUP(C3,{1.0,2.0,3.0},{6.6,5.45,4.54}),IF(L3=4,LOOKUP(C3,{1.0,2.0,3.0},{7.6,6.4,5.34}),IF(L3=5,LOOKUP(C3,{1.0,2.0,3.0},{10.4,8.65,7.21}),IF(L3=6,LOOKUP(C3,{1.0,2.0,3.0},{11.4,9.6,8.01}),IF(L3=0,0)))))))
It works great in excel, but google docs doesn't use LOOKUP I guess. I am wondering if there is a way to rewrite the formula using INDEX and MATCH so that I get the same results?

Here is what I need to cells to = incase the formula above it too confusing.

<style> <!-- /* Font Definitions */ @font-face {font-family:Cambria; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:3 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-ascii-font-family:Cambria; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Cambria; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Cambria; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> L3=1 & C3 =1 then M3=2.80
L3=1 & C3=2 then M3=2.25
L3=1 & C3=3 then M3=1.87

L3=2 & C3=1 then M3=3.8
L3=2 & C3=2 then M3=3.2
L3=2 & C3=3 then M3=2.67

L3=3 & C3=1 then M3=6.6
L3=3 & C3=2 then M3=5.45
L3=3 & C3=3 thenM3=4.54

L3=4 & C3=1 then M3=7.6
L3=4 & C3=2 then M3=6.4
L3=4 & C3=3 then M3=5.34

L3=5 & C3=1 then M3=10.4
L3=5 & C3=2 then M3= 8.65
L3=5 & C3=3 then M3= 7.21

L3=6 & C3=1 then M3= 11.4
L3=6 & C3=2 then M3=9.6
L3=6 & C3=3 then M3= 8.01
Any ideas would be great appreciated. I don't really want to have to put a table in the spreadsheet somewhere, it doesn't give it the look I really want, and it maybe something that google docs can't even do and we will just have to email the excel file back and forth.

 

[Peter_SSs]

I'm not sure about Google Docs but I think this formula achieves the same result as your original one.

=IF(L3=0,0,INDEX(CHOOSE(L3,{2.8,2.25,1.87},{3.8,3.2,2.67},{6.6,5.45,4.54},{7.6,6.4,5.34},{10.4,8.65,7.21},{11.4,9.6,8.01}),MATCH(C3,{1,2,3})))

 

[headspinning]

That formula works great for C3=1 but when C3=2 or C3=3 it gives me #Ref as my answer.

 

[Peter_SSs]

Can C3 only be 1 or 2 or 3?
.. or could it be 2.4 or 6.7 or 0.33 etc?

 

[Peter_SSs]

That formula works great for C3=1 but when C3=2 or C3=3 it gives me #Ref as my answer.

Do you mean in Excel or Google Docs? As I said, I don't know about Google Docs and I was giving you an alternative in Excel without LOOKUP to try. It works for me in Excel:

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M
3	3									4	5.34
headspinning

 

[headspinning]

C3 can only = 1, 2 or 3

 

[AlphaFrog]

This is my go at it...

=INDEX({2.8,2.25,1.87;3.8,3.2,2.67;6.6,5.45,4.54;7.6,6.4,5.34;10.4,8.65,7.21;11.4,9.6,8.01},
             MATCH(L3,{1,2,3,4,5,6}), MATCH(C3,{1,2,3}))

Array constants in Excel

 

[headspinning]

The example you show is what I am trying to get, and I can get it to work in Excel just not in google docs. It would make it easier to upload documents to google if they would let everything work like it does in excel.

 

[headspinning]

Alpha Frog- That worked. Thanks to both of you for answering my question :D This site has been wonderful in helping!!!

 

[Peter_SSs]

The example you show is what I am trying to get, and I can get it to work in Excel just not in google docs. It would make it easier to upload documents to google if they would let everything work like it does in excel.

So maybe Google Docs doesn't like CHOOSE or INDEX or MATCH?

Try each of these in Google docs and report the results

=CHOOSE(2,5,6,7)
=INDEX({11,21,31},2)
=MATCH(3,{0,1,2,3,4})