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.

 

[AlphaFrog]

You may not need the MATCH functions depending on your data.

=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},
             [COLOR="Red"]L3,C3[/COLOR])

 

[Peter_SSs]

You may not need the MATCH functions depending on your data.

=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},
             [COLOR="Red"]L3,C3[/COLOR])

I was half way through typing a response to suggest just that when you posted.

 

[Peter_SSs]

I note that the original formula had a condition for if L3= 0. This latest formula does not contain that but of course that could easily be added by a simple IF().

For what it's worth, here's another method without any INXEX/MATCH/CHOOSE that I think also achieves the desired result, including if L3=0.

=IF(C3=1,2.8,IF(C3=2,2.25,1.87))*(INT((L3-1)/2)+1)+IF(C3=1,1,IF(C3=2,0.95,0.8))*((INT((L3-1)/2)+1)-MOD(L3,2))

 

[headspinning]

I had noticed that the equation was missing the =0 portion too so I had added it.

Thank you both for your help. I am so glad that this is going to work using google docs. Sharing it via email was not going to be fun.

 

[Peter_SSs]

Somewhat shorter.

=INDEX({2.8,2.25,1.87},C3)*INT((L3+1)/2)+INDEX({1,0.95,0.8},C3)*INT(L3/2)