Another IF question.

csappington

New Member
Joined
Dec 15, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello, I have different prices for each unit that I need to use depending on which Substation Line we are working on. Lets call the lines A, B, C, D, E, F, and G. I would like to enter the Substation Line that I am billing for in the Yellow Box, then those corresponding Substation Line unit prices will fall below next to each unit indicated in blue.

I have messed with the IF formula and the false option is throwing me off.

Basically if E3=A then E4="removal!C3". Then I can drag that down through the worksheet. After I F4 cell E3.
Or if E3=B then E4="removal!D3". Then drag that down too.
Pictures are attached.

Can anyone teach me how to do this? Much appreciated!
 

Attachments

  • Excel Q1.JPG
    Excel Q1.JPG
    90.4 KB · Views: 3
  • Excel Q2.JPG
    Excel Q2.JPG
    156.9 KB · Views: 3

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,832
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=INDEX(Removal!$C$3:$I$100,MATCH(D4,Removal!$B$3:$B$100,0),MATCH(E$3,Removal!$C$2:$I$2,0))
 

csappington

New Member
Joined
Dec 15, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=INDEX(Removal!$C$3:$I$100,MATCH(D4,Removal!$B$3:$B$100,0),MATCH(E$3,Removal!$C$2:$I$2,0))

That works perfectly! Thank you very much!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,832
Office Version
  1. 365
Platform
  1. Windows
Will the col D values on sheet1 always be exactly the same as the col B values on Removal?
ie no missing values & in the same order
If so you could use
Excel Formula:
=FILTER(Removal!$C$3:$I$100,Removal!$C$2:$I$2=E3)
 

csappington

New Member
Joined
Dec 15, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Will the col D values on sheet1 always be exactly the same as the col B values on Removal?
ie no missing values & in the same order
If so you could use
Excel Formula:
=FILTER(Removal!$C$3:$I$100,Removal!$C$2:$I$2=E3)
They were given to us all in the same order luckily! Thank you again. Great job.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,832
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,092
Messages
5,640,066
Members
417,125
Latest member
sfreind

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
Top