# Find Value In Table & Return Column / Row Header

Nov 23, 2009

I am having trouble with getting a value (which is the row and column value) from a set of numbers. I have attached an example table to try and make things clearer.
What I have is a table with a set of Row Headers, and Columns Headers all numeric. e.g Columns headed 1, 2, 3, 4, Rows 30, 40, 50 etc. I want to find the 1st occurrence of a value e.g. 1.0 in the table (by 1st Up mean closest to top left), and then give me the value of the Row and Column Header that corresponds to that.

So for the example attached, I want to get the row Value 200 and Column 6 from finding the 1st occurrence of the number 1. I have tried various combinations of index and Match etc. But I can't get my head around the fact that the position of the 1st no. 1.0 can change, therefore I can't use any releative positions etc.

## Find Last Entry In Row And Return Column Header?

Aug 1, 2014

I've got a spreadsheet with various amounts in cells A3:L5. I want to find the last non-blank entry in each row but only where the entry is in a column headed "Guaranteed PRB" (found in row 1). Then I want it to return the column header title found in row 2, which is a date.

I've attached a sample of the spreadsheet with the expected answer in column M.

I've got as far as formula:

=LOOKUP(2,1/(a3:l3<>""),\$a\$2:\$l\$2)

how to also make it look at row 1's headers too.

## Find First Non Blank Cell In Row Then Return Column Header?

Jan 17, 2014

I have this table, which can be seen as a basic custom gantt chart: KLRWo.png

And I would like to fill the A column with start dates, based on the first filled cell of the range on the same row, and the header value of its respective column (row 1). It's easier to show my expected result than write it actually:

WiMZH.png

Nov 18, 2011

I've been trying find an appropriate formula to extract the column header from a table in a different sheet if the row header and value in that table is known.

in the lookup table the row titles (column A) are product codes, column titles (row 2, D through AX) are business names and the table values are quantity.

In a different table I have product codes in column A and in column B i have the max number/quantity of products for that code. In column C i want to put the company name associated with the product and the number/quantity.

## Find X In The Corresponding Row And Return The Header Value

Apr 28, 2009

I need a formula to put into cell B19 to Find X in the corresponding row and to return the header value

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows .NET Server (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEF1Part NumberDescriptionSubframe BuildCut And ChopHopper BuildCabBuild2026319Assy Air Ram Auto Blanking  X 3026321Assy Air Ram Rear Boom L/H  X 4026320Assy Air Ram Rear Boom R/H  X 5022587Assy Air Ram Side BrushX   6031159Assy Air Ram Side Brush Large DiameterX   7030501Assy Blanking Flap Valve Single Vm Minor    8034783Assy Brake Pedal Lh Vm Minor Fl   X9034784Assy Brake Pedal Rh Vm Minor FlX   10036349Assy Brush Control D/S No Hp Opt Mer/Mag X  11024553Assy Brush Core 1300 Large Widesweep X  12024554Assy Brush Core 1300 Small Widesweep X  13023169Assy Brush Core Large 7000 X  14025662Assy Brush Core Large 80/85 X  15032482Assy Brush Core Magnum Poly/Wire Mix X  16      17      18      19034784Subframe Build    20023169Cut And Chop    Sheet1 [HtmlMaker 2.41] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

## Find Cells That Contain Special Character In Given Row - Return Corresponding Header(s)

Jul 21, 2014

I'm looking for a VBA Macro that will do the following:

1. Count number of cells in a given row that contain special character "*"
2. If there is an asterisk in a given row, return the column header(s) contained in row 1 of the spreadsheet that correspond to each instance of cell(s) that contains the "*".

Is there a way to do this? I've attached an example of what I'm talking about, (Column A shows the desired result.)

Formula Question.xlsx

## Find Matches In Multiple Columns And Return Header

Oct 29, 2012

Lets see if I can put this into words.

I am trying to find matches of a specific cell in various columns. Example:

-ABC123-----------abc123--------------abd123
-abd123-----------hjk321---------------hdn234
-Abc123-----------dsd123--------------sds332

If I searched for the value "abc123" I want it to return Headers 1 and 2 in a seperate column. It would not matter if the same value is in one column multiple times

So the results would show me the Column Heading for anything that reads: "abc123", "ABC123", "AbC123", "aBC123"

Is this possible?

## Return Column Header Based On Column Criteria And Number Value

Feb 7, 2014

I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).

Here's data table:

ZZZ5.0015.0050.00130.00
ZZ5.0015.0050.00130.00
Z5.0015.0050.00130.00
YYY5.0015.0050.00130.00
YY5.0015.0050.00130.00
Y5.0015.0050.00130.00
DDD5.0015.0050.00130.00
DD5.0015.0025.0075.00
D5.0015.0020.0065.00
RRR5.0015.0015.0045.00
RR2.5010.0010.0030.00
R1.503.0010.0025.00
UUU0.751.505.0020.00
UU0.751.505.0020.00
U0.751.505.0020.00
P0.751.505.0020.00
T0.100.105.0018.00

CriteriaNumberValue

## Formula To Return Column Header If There Is Data In The Column

Sep 18, 2012

I need a formula to return column header if there is data in the column, any data.But I also need to return the second, third, fourth header with data too

In column A row 2, I have =IF(M8"",\$M\$1,IF(N8"",\$N\$1,IF(O8"",\$O\$1,IF(P8"",\$P\$1,IF(Q8"",\$Q\$1,IF(R8"",\$R\$1,IF(S8"",\$S\$1,IF(T8"",\$T\$1,""))))))))

This returns the row 1 header for the first instance of data in row 2 columns M thru T But in B2, I need the second header with data in the column.

I need the results in yellow (1st, 2nd, 3rd, 4th) from the array to it's left

1St match2nd 3rd 4thColumn MColumn NColumn OColumn PColumn QColumn R
Column MColumn PColumn QData data2Data3
Column NColumn OColumn PColumn R Data Data2 data3 data4

Would use match/index but can't increment it to the second or 3rd match

## Formula To Return Column Header?

Jul 7, 2014

I am looking at formula to return the column header, see example attached.

## Right Most Cell With A Value And Return Column Header?

Dec 11, 2012

My file is set up like this:

Deal Jan Feb Mar....... Dec
A 0 11 0
B 0 0 23
C 13 0 12
and so on

Meaning deal A got cash of 11 in Feb and B of 23 in March

For a certain month (which could change), I want to specify the most recent month of a payment and return the month

So in April, row A returns 2, Row B March and Row C March

## Return Value From Column Based On Header Value?

Jul 16, 2013

Cells A3:A500 contain product numbers, e.g. 1001, 1002, 1003 etc.. etc...
Cells G2:N2 are column headers, e.g. North2011, South2011, North2012, South2012 etc... etc...
Cell F2 is a lookup that will return a year, e.g. 2011, 2012, 2013 etc... etc...

Against each product is a "yes" if it was sold in the north or south region for each year. This could be both north and south, one or the other or none at all.

What I would like is a formula in cells E3:E500 that looks at the year in cell F2 and if there is a "yes" in either the North or South for that year to return "yes" otherwise blank.

I am unable to upload a sample document due to firewalls.

## Return Column Header From If Statement

Jan 6, 2012

I'm working on a spreadsheet where I need to return the column header where the value in that row matches the criteria from an if statement.

The column headers are departments, the row headers are customers numbers, the values show the % of items sold in that department as a % of the total purchases by that customer.

I need to make a formula for each row so that if the % in any of the current row's values is 75%-100% then get the column header for the column that value is in.

I know it's an IF statement, but I don't know how to return the column header for the value when the if statement is true.

## VLookup Return Column Header Name

Mar 30, 2012

I'm trying to get all subjects that are "pass" per student #.

Student #MathScienceHistoryStatisticsPhysical EducationHealthPhysicsEconomicsTrigonometryEngineering
5200309PASSPASSPASS5200306PASSPASSPASSPASSPASS5200307PASSPASSPASSPASSPASS
5200305PASSPASSPASSPASSPASS5200257PASSPASSPASS3046109PASSPASSPASSPASS3045539PASSPASSPASSPASS
3045613PASSPASSPASSEnter Student #

If I type student number here the result will be shown below with all subject passed.Subject Passed

## Return ALL Non-zero Cell VALUE And Corresponding Column Header For All IDs

Oct 31, 2013

I have a table like the following where each ID can have no, 1 or multiple values associated with it:

Table1
ID>>>A>>>B>>>C>>>D
1>>>> >>> >>>10>>>
2>>>> >>> >>> >>>>50
3>>>5>>> >>> >>>>2
4>>> >>> >>> >>>>

I have another table that only lists IDs
Table2
ID
1
2
3
5

I want a VBA code such that if ID is in Table2, VBA looks for the matching ID in Table1 and if the matching ID found, it looks for ALL non-empty cells and if a non-empty cell is found in the row, it returns the value in the cell and the header to give something like this

Table3
ID>Column1>Value1>Column2>Value2
1>>>>c>>>10>>
2>>>>D>>>50>>
3>>>>A>>>5>>>>>>D>>>>>>>2>>>

## Return Column Header Based On Row Value

Mar 11, 2014

Requirement
Vendors
Evaluation Results

Sr.
ITEM DESCRIPTION
BRAND
UNIT
A-Z
AL AMEEN
COLOR WORKS
INDEX
OXFORD
PENCIL
SUPPLIES STORE
WAVES
LOWEST PRICE
SECOND LOWEST
Vendor Lowest Price
Vendor 2nd Lowest Price

[Code] .......

I have above table and wants to get the formula for getting the 1) Vendor Name of the Lowest Price per item 2) Vendor Name of the 2nd Lowest Price per item 3) and be able to copy down the formula for other items.

## Look Up In Sheet2 And Return The Header Of Which Ever Column It Is Under

Dec 18, 2009

I have on sheet1 a name "brent alpha" which I need to look up in sheet2 and return the header of which ever column it is under,

Sheet 2 consists of 3 columns with a list of data under each one. As I said all I want to do is return the header.

## Return Column Header Name For First Entry On Each Row Greater Than Zero

Feb 12, 2014

I'm currently using the formula

=IFERROR(INDEX(startmonthsp,MATCH(TRUE,INDEX(ISNUMBER(E2:AR2),0),0)),"").

The startmonthsp name refers to the months across the top of my pivot table. This formula works but I now need one small change. If the value in the columns are zero or less then I do not want the formula to return that column header (bypass it until it gets a positive value above zero).

Attached is a workbook that shows my formula in action.

Excel Forum Question.xlsx

## Return Column Header Of Last Positive Value In Range

Oct 21, 2009

I've got numerical data in columns A through AF, starting in row 4. I need a formula, starting in AG4 that I can drag down. I need the formula to look back across the row until it gets to the last posistive number, then return the column header of that column (the column headers are in row 3)

so for example

Jan Feb Mar Apr May Jun Jul Aug

Data

0 3 0 0 8 0 0 0

the formula would return May in this example as 8 is the las positive value and the column header for that column is 8. There are no blank cells in the data.

Nov 18, 2008

I have a list of names and the chores they need to do on a certain day. I need a formula that returns the chore the person needs to do when the date is filled in.

Ex: Sheet2 Col:A has names filled in A2:11 , row B1:J1 has dates that you would input.
formula goes in B2:J11
Sheet1 is the master with all the data the formula would be pulling from.
column A2:A11 has the names , row B1:S1 has the chore that needs to be done.
B2:S11 has the dates already filled in.

## Return Column Header Of First Column That Doesn't Contain 0

Feb 9, 2009

I'm looking for a formula that will return the column header (a date) of the first instance of a number greater than 0. For example if columns A through S had dates as in row 1 (header row), and in row 2, every column contained a 0 up until column P, I would want the date (Row 1, Column P) returned? I think this might be a sort of index/match formula, but I am not very familiar with these.

## Script To Find Column Header Doesn't Work If It Is Second To Last Or Last Column

Mar 3, 2014

[Code] ......

This works, UNLESS "My Column Header" is the last column, or second to last column, then it jumps left two columns, instead of landing on the correct column.

The purpose of this script is to select a cell directly in that column that I was searching for.

## ID First And Last Non Blank Values In Row And Return That Cells Column Header?

Mar 13, 2014

I have a data set with the weeks of the year as my column headers and a value of 1 (from a pivot table) in a distinct row value (removed for privacy) showing whether it existed in a given week based on pre defined criteria. What i need to do is ID the first (from the left) non blank cell in each row and then return that cells column header. I then need to do the same for the last (from the left) non blank cell in each row and then return that cells column header. On the attached I've put values in column N and O to show an example of what I need to return using a formula.

## Scan Row For Text - Return Row And Column Header Info

Jun 2, 2013

I've been using a nice colorful spreadsheet to use to schedule rooms for an organization. Visually it has worked out well but now I need to automatically generate a 'report' using that data. So...

I want to scan each row of a table and create another table with that data transformed.
That is, scan each row and whenever I find any text in a cell I want to return that text (the room being used) as well as the row and column header information (who is starting to use that room and on what date). I've attached a jpeg to show the example.

Ideally, when I scan the row and find text to return the headers I'd like the row scan to also find when the fill color changes and return that column header, too as that that would be the date that the room stops being used.

## Recognizing Column Header In Table As Date?

Sep 29, 2013

Perhaps I missed something in the basics of Excel as to how a table's column header is formatted.Also, my date format is dd/mm/yyyy

When using a basic formula, such as below, in A1 and Cells A2 and B2 have the dates 1/01/2013 and 2/01/2013, this code works fine.

VB : =If(A2<=Today(),"Yes";"No")

BUT, when you convert those two dates to a table, the formula with post No.

However, if you make another formula as show below, the correct day will come up.

VB : =Text(A2,"dddd")

When auto filling the dates in the row, without a table, the dates increase per day.

When auto filling the dates in the row, with a table, the dates increase per year. (And yes, even when 3+ dates are inserted, highlighting those 3 and then autofill.)

Why does a column header change the format and to what format is it changed to?

Why do some formulas output accurate date based on the date in the column header and others do not?

Why do the dates increase per day without a table and per year with a table?

## Return Row Heading By Column Header And Specific Texted Cell Row Position

Jul 11, 2014

I have a data set, in which Sheet 1 have a matrix of high level showing Engineer, Program and commodity relationship.

I have to create separate sheet for each program on sheet 1, each program sheet should have Engineer name and commodity name from sheet 1.

Multiple commodities and multiple engineers should be separated by definite structure.

find worksheet with input and output (manual) as expected.

## Find First Visible Cell Under Header Of Column A?

Jul 1, 2014

ive added a filter, and on column A i want to find the first blank row under the header and type the word 'tech' and fill down to the last row populated against column b.

## Pivot Table - How To Use Same Data For Filter And Column Header

May 13, 2014

I have a list of S/N with Pass/Fail result next to it. A specific S/N can appear several times in the list:

SN...Result
-------------
111...FAIL
111...FAIL
111...FAIL
111...PASS
222...PASS
333...FAIL
333...FAIL

When creating a pivot table of SN vs. Result (count) I get:

****|FAIL...PASS
-------------------
111..|...3.....1
222..|..........1
333..|...2

I want to get the FAIL count of SN in which PASS > 0 (First two rows)

BUT I can not figure out how I can filter based on the values of the pivot table itself (rather than the values of the original list). When I try to use 'Result' field as report filter, I can not get it as a Column label (and vice-versa). In this case I get:

I get:
****|PASS
------------
111 | ...1
222 | ...1

Desired:

.......|..FAIL..PASS
-------------------
111 |...3...1
222 |........1

Desired (alternative):

......|..FAIL
-------------
111 |...3
222 |...0

## Lookup Column Header Value Based On Matching Table Value

May 16, 2013

I'd like a formula that'll return the column header by matching a lookup value with a table in the second sheet.

eg: sheet 1

Name
Cell
Region

John
111-2222

[Code] .......

The formula should match the name in A2, John, with value from the table in sheet 2 and return the correct region, this case North.

## VBA / Find Column Header And Reformat (date) Values In Columns

Jun 28, 2013

I have VBA code to perform some actions on data in excel file and then convert all that data into semi-colon separated CSV/text file (code below).

Now, all I want is to add VBA code in the existing macro to find a column header (say, "Application date") and then convert all the dates into YYYY-MM-DD format. The original values in this column don't have a fixed date format.

Code:
Public Sub ExportToCsvFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendDataOnExistingFile As Boolean)
Dim WholeLine As String Dim FNum As Integer
Dim RowNdx As Long Dim ColNdx As Integer

[code]....