look up values

Arch009

New Member
Joined
Dec 23, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I am trying to create a "% complete" chart where the column on the lefts reads to right. I need it looks for the value of "x" in a cell within the row to the right and then report the % number in the header above it. I am using the xlookup and got it to report the % complete. I'm not to familiar xlookup, so maybe there is a better way. I have rows I need to keep but need them to not to report 0% or 100% as this will affect the "Overall % Complete" when it counts them. How do I get the % Complete to read on the Percentage?

=XLOOKUP("x",H31:CN31,$H$27:$CN$27,1) - my formula to get the % complete

=IF(XLOOKUP("x",H28:CN28,$H$27:$CN$27,1),"","") - this returns blank cells and I can't figure out how to get the % to show up.

=AVERAGE(G28:G46) - provides overall % complete in g47

1608748016440.png
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi & welcome to MrExcel.

For future reference, MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

How about
Excel Formula:
=XLOOKUP("x",H28:CN28,$H$27:$CN$27,1)
 
Upvote 0
Book1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAX
1
2
3East Facility% Complete0%5%10%15%20%25%30%35%40%45%50%55%60%65%70%75%80%85%90%95%100%
4114 - Headworks and Influent Pump Station Bldg30% x
5116 - Grit Removal Bldg35%35%x
6117 - Grit Removal System (Exterior Process Equipment)0%xNA
7118 - Primary Clarifier 1 and 25%5%x
8120 - Primary Sludge Station Bldg0%xNA
9121 - Flow Diversion Structure #30%xNA
10122 - Blower Bldg0%xNAx
11125 - RasWas Pump Station Bldg40%40%x
12126 - Biosolids Bldg35%35%x
13127- Admin Bldg30%30%x
14130 - UV Bldg40%40%x
15132 - Anoxic Basin0%xNA
16134 - Aeration Basin 1 and 20%xNA
17136 - Post Anoxic / Post Aeration Basin 1 and 25%5%x
18138 - Biosolids Blending Tank0%xNA
19140 - Sludge Storage Tanks 1 and 20%xNA
20142 - Sludge Storage Tanks 3 and 40%xNA
21144 - Sludge Transfer Pump Station and Chemical Feed Bldg???0%x
22146 - Sludge Transfer Station Bldg0%x
2328%11%
24
25
26
27
Sheet1
Cell Formulas
RangeFormula
G4G4=IF(XLOOKUP("x",H4:CN4,$H$27:$CN$27,1),"","")
G5:G22G5=XLOOKUP("x",H5:CN5,$H$3:$CN$3,1)
F23:G23F23=AVERAGE(F4:F22)
 
Upvote 0
Hi & welcome to MrExcel.

For future reference, MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

How about
Excel Formula:
=XLOOKUP("x",H28:CN28,$H$27:$CN$27,1)
I am using the formula you suggested but where are suggesting to place it at? I have tried several places and formulas error out. I have posted the excel file thru the tool you suggested. See other post.
 
Upvote 0
Thanks for using XL2BB, If you put
Excel Formula:
=XLOOKUP("x",H4:CN4,$H$3:$CN$3,"",0)
in G4 & copy down, does that give what you want?
 
Upvote 0
Thanks for using XL2BB, If you put
Excel Formula:
=XLOOKUP("x",H4:CN4,$H$3:$CN$3,"",0)
in G4 & copy down, does that give what you want?
I did that but it still leaves 0% in the cells. If there is 0% in a cell that will not have % complete charted. this will cause problems with G23 average and will count the 0%. this is where i need an If then formula. I tired this =IF(XLOOKUP("x",H28:CN28,$H$27:$CN$27,1),"","") and something similar but could not figure out.
 
Upvote 0
If you just want to ignore the zeros in the average use
Excel Formula:
=AVERAGEIF(G4:G22,">0")
 
Upvote 0
If you really need to get rid of the 0% try
Excel Formula:
=IFERROR(1/(1/XLOOKUP("x",H4:CN4,$H$3:$CN$3,"",0)),"")
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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