Combining If THEN and VLOOKUP

mrsadventure

New Member
Joined
Mar 22, 2019
Messages
1
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 UpsScorePush UpsScore
36523690
35503588
34493486
33483384
32463283
31453181
30433079
29422977

<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>
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,310
Office Version
  1. 365
Platform
  1. Windows
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 />
 

Watch MrExcel Video

Forum statistics

Threads
1,108,991
Messages
5,526,099
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top