Combining If THEN and VLOOKUP

[mrsadventure]

I am in desperate need of help. I am a novice at excel formulas. I need to write a formula that will pull from a set of data the separates between a Male or Female, Number of Push Ups and then provides a score...so for example:

M	F
Push Ups	Score	Push Ups	Score
36	52	36	90
35	50	35	88
34	49	34	86
33	48	33	84
32	46	32	83
31	45	31	81
30	43	30	79
29	42	29	77

<colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody>
</tbody>

So if I am in a new spreadsheet, first column (A) would say M or F, then I will enter data in column (B) then I need Column C to show the corresponding answer, for example

<tbody>
</tbody>

A	B	C
M	36	using the data above should calculate and show "52"
F	30	using the data above should calculate and show "79"

<tbody>
</tbody>

 

[Fluff]

Hi & welcome to MrExcel
How about

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">A</td><td style=";">B</td><td style=";">C</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">M</td><td style="text-align: right;;">30</td><td style="text-align: right;;">43</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">F</td><td style="text-align: right;;">36</td><td style="text-align: right;;">90</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">Sheet1!$A$1:$D$1=A2</font>)*(<font color="Red">Sheet1!$A$3:$D$10=B2</font>),Sheet1!$B$3:$E$10</font>)</td></tr></tbody></table></td></tr></table><br />

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: center;;">M</td><td style="text-align: center;;"></td><td style="text-align: center;;">F</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: center;;">Push Ups</td><td style="text-align: center;;">Score</td><td style="text-align: center;;">Push Ups</td><td style="text-align: center;;">Score</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;;">36</td><td style="text-align: center;;">52</td><td style="text-align: center;;">36</td><td style="text-align: center;;">90</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;;">35</td><td style="text-align: center;;">50</td><td style="text-align: center;;">35</td><td style="text-align: center;;">88</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: center;;">34</td><td style="text-align: center;;">49</td><td style="text-align: center;;">34</td><td style="text-align: center;;">86</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: center;;">33</td><td style="text-align: center;;">48</td><td style="text-align: center;;">33</td><td style="text-align: center;;">84</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: center;;">32</td><td style="text-align: center;;">46</td><td style="text-align: center;;">32</td><td style="text-align: center;;">83</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: center;;">31</td><td style="text-align: center;;">45</td><td style="text-align: center;;">31</td><td style="text-align: center;;">81</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: center;;">30</td><td style="text-align: center;;">43</td><td style="text-align: center;;">30</td><td style="text-align: center;;">79</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: center;;">29</td><td style="text-align: center;;">42</td><td style="text-align: center;;">29</td><td style="text-align: center;;">77</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />