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
View 9 Replies
ADVERTISEMENT
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.
View 4 Replies
View Related
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:
CriteriaHeader 1Header 2Header 3Header 4
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
DDD10.00Header 1>>>=INDEX($I$9:$L$9,MATCH(I29,INDEX($I$10:$L$26,MATCH(H29,$H$10:$H$26),)))
View 3 Replies
View Related
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
View 2 Replies
View Related
Jul 7, 2014
I am looking at formula to return the column header, see example attached.
View 2 Replies
View Related
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
View 2 Replies
View Related
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.
View 5 Replies
View Related
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.
View 9 Replies
View Related
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>>>
View 1 Replies
View Related
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.
View 5 Replies
View Related
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.
View 9 Replies
View Related
Jan 29, 2014
I'm trying to pull information in one spreadsheet (SS1) from another spreadsheet (SS2). I've found that the following works, except when SS2 is closed. Is there a way to do the following formula so that it will work even if SS2 is closed?
=VLOOKUP(A19,A:H,MATCH("Column Header",OFFSET(1:1,MATCH("Row Header",A:A,0)-1,0),FALSE),FALSE)
Link A19 is the lookup value
"Column Header" is the column header we want to find
"Row Header" is the unique row header for the headers of the table
The Column Header could appear anywhere in Column A to H, and the Row Header can appear on any row in the spreadsheet!
View 3 Replies
View Related
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.
View 5 Replies
View Related
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
View 2 Replies
View Related
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
Header row
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.
View 9 Replies
View Related
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.
View 5 Replies
View Related
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.
View 3 Replies
View Related
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.
View 3 Replies
View Related
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
View 6 Replies
View Related
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.
View 7 Replies
View Related
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.
View 5 Replies
View Related
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.
View 2 Replies
View Related
Feb 8, 2010
I want to get value from reference one column and return value by corresponding row from another column. This can be done by vlookup column. But it find value from 1st column and return value from given relative column. That mean find from Left column and return value from right side given column number.
But I want find from Given column number and return value from given left column. For Further detail see the Attached file.
View 3 Replies
View Related
Apr 6, 2007
=IF(ISNUMBER(VLOOKUP($E8,Su!$D:$K,7,FALSE)),VLOOKUP($E8,Su!$D:$K,7,FALSE),)*$I8
In the first vlookup it picks up a value. I need to do the same vlookup function but it should also pick up another value in column 9 and should add value in column 7 and 9 and return it. Employee id numbers are in column E in the first page.My formula checks for the employee id in Su sheet and retrieves the value from the seventh column. I need to retrieve also from 9 th column and add them together.
View 8 Replies
View Related
Apr 4, 2014
I want to look up a value in the third column of a table and return the value in the first column of that row on the same table. What do I do?
View 2 Replies
View Related
Jul 6, 2007
Is there a way where i can vlookup a column and return all matches if there are multiple values?
View 3 Replies
View Related
Oct 7, 2008
I'm looking for a formula to search for a value in the SECOND (instead of first) column of a table array and return a value in the same row from FIRST (instead of the same or another) column in the table array. Formula would be searching for the unique production order number in the column B and return production line id from the column A.
A1 production line_id
B1 poduction_order_number
A2 L1
A3 L2
A4 L1
B2 505212
B3 504234
B4 505663
I was trying vlookup(504234;B2:A4;2;0) to make formula go search from right columns to the left but then excel is switching the search table to A2:B4 and gives #N/D!
One remark-there is no possibility to switch these 2 columns to simplify. I have to leave them as they are.
View 2 Replies
View Related
Jul 14, 2009
I am running a Vlookup on a large set of ID#'s that exist accross multiple servers.
The data is structured in (2) columns how you see below , and you will notice that the same ID# can show up on multiple servers.
However, the VLOOKUP is only returning the first match it finds.
Assuming that I want to keep the data structured as is, how do I make the vlookup return all matches?
ID# LOCATION
23 SERVER1
34 SERVER4
23 SERVER2
13 SERVER1
17 SERVER3
34 SERVER1
View 10 Replies
View Related
Jul 7, 2014
Sheet 1
Sheet 2
UPC
Sku
[Code].....
I would like to find the value from Sheet2 Column1 in sheet1 Column1 and return value from Sheet1 Column2 and Column3 into Sheet2 Column2 and Column3
And if it doesn't find anything just return Not Found
The problem that a Vlookup is not working for me is because I want it to be the exact text from sheet2 column1 but in sheet1 column 1 it should not be exact as it might have some extra text as seen in the illustration above
View 3 Replies
View Related
Aug 27, 2012
I have a column (B) of randomly generated numbers 1-14, and am using this formula range to sort in descending order and return the relevant value from column (c).
=VLOOKUP(LARGE(B3:B20,1),B3:C20,2,) to =VLOOKUP(LARGE(B3:B20,14),B3:C20,2,)
Works great, except when a number is duplicated, (E.G. 14,13,13,12,11,10,10,9,8 etc). It then returns the first value from(C) repeated, and not the value from the second and subsequent duplicated reference numbers.
View 3 Replies
View Related
