Formula Referencing Table Header on Two Lines

sspatriots

Well-known Member
Joined
Nov 22, 2011
Messages
569
Office Version
  1. 365
Platform
  1. Windows
My table has a column with the header shown below:

Cab
PO


PO was moved to the next line when it was created by using the "Alt + Enter" key.

When I use the formula below, it works:

=IF(ISBLANK(VLOOKUP(G2JobList[[#Headers],[Cab
PO]],Table7[#Data],14,FALSE)),"Is blank","not blank")

I would prefer that my formula didn't automatically wrap to the next line when I selected the lookup_value for the table column header, however, I realize that it is doing that because of "Cab" and "PO" being on two separate lines in the header. I tried re-writing the formula as follows:

=IF(ISBLANK(VLOOKUP(G2JobList[[#Headers],[Cab & Char(10) & PO]],Table7[#Data],14,FALSE)),"Is blank","not blank")
=IF(ISBLANK(VLOOKUP(G2JobList[[#Headers],[Cab" & Char(10) & "PO]],Table7[#Data],14,FALSE)),"Is blank","not blank")
=IF(ISBLANK(VLOOKUP(G2JobList[[#Headers],["Cab" & Char(10) & "PO"]],Table7[#Data],14,FALSE)),"Is blank","not blank")

=IF(ISBLANK(VLOOKUP(G2JobList[[#Headers],[Cab & Chr(10) & PO]],Table7[#Data],14,FALSE)),"Is blank","not blank")
=IF(ISBLANK(VLOOKUP(G2JobList[[#Headers],[Cab" & Chr(10) & "PO]],Table7[#Data],14,FALSE)),"Is blank","not blank")
=IF(ISBLANK(VLOOKUP(G2JobList[[#Headers],["Cab" & Chr(10) & "PO"]],Table7[#Data],14,FALSE)),"Is blank","not blank")

None of these will do the same thing as the original formula that was entered into the cell with the "Alt + Enter" to move "PO" down to the next line to match the column header. I need this to work this way because I want to put it into a macro formula to populate the cell for me at some point because sometimes we have people that will accidentally delete formulas or overwrite them. Any ideas how to make this work using something similar to the 6 formulas I've tried?
 

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.
I ended up with the following and it seems to work just fine for the structured references...

=IF(ISBLANK(VLOOKUP(INDEX(G2JobList,ROW(),MATCH("Cab" & CHAR(10) & "PO",G2JobList[#Headers],0)),Table7[#Data],14,FALSE)),"Is blank","not blank")
 
Upvote 0
Solution

Forum statistics

Threads
1,214,911
Messages
6,122,195
Members
449,072
Latest member
DW Draft

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