How to write Vlookup with IF statements

THolzschuh

New Member
Joined
Mar 28, 2017
Messages
3
Hello - I am new to the forum and hoping that someone can provide guidance on how to write a formula that would would include an IF and Vlookup. I'm not sure if this is the best approach to the formula required but that's were my mind is at.


Below is what I'm faced against. I'm trying to determin the # of manhours it would take based on the level (OLT or ILT) and have the result of man hours pulled from the chart. For example: The level is OLT and the outcome level is 1.2. I would want the manhours to pull from the chart below and the answer returned should be 218. However, if the Level was not OLT but instead ILT I would want it to return the number manhours of 200


A
B
C
D
1
Outcome Level
1.2
2
Level
OLT
3
Manhours
formula needed here
4
5
Outcome Level
ILT
OLT
6
1.0
165
181
7
1.1
182
200
8
1.2
200
218
9
1.3
208
237
10
1.4
233
256

<TBODY>
</TBODY>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Got it. I understand what your saying and it does work. Thank you.
Now I realized that I need to change some of the existing calculations and which cells I'm pulling from and I'm getting a #NA error.
I've changed row 2 and added row 3 with a formula. Now I need take cell B3 and use that average to pull from the chart. I still need it to pull the "ILT" catagory in but that is acutally pulling from a different cell on a different tab. I had removed it from this view to clean it up a little.

The forumula I tried to type but did not work was: =VLOOKUP(B3,A6:C10,IF(D1="ILT",2,3),0)
any thoughts?

A
B
C
D
1
Outcome Level
1.2
ILT
2
Complexity Level
2.0
3
avg hours
=average(B1:B2)
4
Manhoursformula needed here
5
Outcome Level
ILT
OLT
6
1.0
165
181
7
1.1
182
200
8
1.2
200
218
9
1.3
208
237
10
1.4
233
256

<TBODY>
</TBODY>
 
Upvote 0
It gives an error because the value in b3 is not present in column A
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top