Need Lookup Formula To Give Column Range
Jul 26, 2014
I'm trying to write a formula that searches for the name of the column on one sheet, finds that column on another, then results in the column letter. So, ideally, if the first three columns in the first tab were "Team," "Name," and "Total," respectively, I just want a formula to search for "Name" and give me back "B."
I want to do this in case anybody ever moves the "Name" column, for example, from column B to column C -- so then it would search for "Name" again and change the range in the formula to "C" because that's where that data lives now.
Currently, I'm using it for a COUNTIF() function that is a very simple: =countif(indirect("'"&$A2&"'!K:K"),$B2), where A2 is the sheet name. The latter half of $B2 is fine -- but I would love to have a more complicated formula take the place of the range "B:B" in case the "Name" column ever got moved.
View 8 Replies
ADVERTISEMENT
Jan 13, 2014
Is there a lookup formula that will give me multiple items? so in my example attachment i want to look up risk from section A to give me the item in section B, but for risk 7 for example it has multiple items (a,b,c), how would i get all them? even better would be if its possible to have in seperate cells
View 4 Replies
View Related
Feb 7, 2008
I need a formula (or formulas) to give me the "coordinates" (Column & Row location) of a certain value in a large rectangular range. Intuitively this should be doable with a smart combination of VLOOKUP, HLOOKUP, INDEX, MATCH, ADDRESS and/or CELL functions (maybe in the form of "array formulas") but I can't seem to find the way...
Here is an example of what I need:
I have a large rectangular range (A10:F500), full of numerical values. Then in cell A1 I have one of the (many) values contained in the range, let's say, 3.14159. I need two formulas that give me the following:
a) A formula in cell B1 that tells me in which COLUMN of the range the value 3.14159 is located
a) A formula in cell C1 that tells me in which ROW of the range the value 3.14159 is located
If the range consisted of a single column (or a single row), this would be an easy INDEX and MATCH combination.... but I'm dealing with a *rectangular* range here...
View 9 Replies
View Related
Jun 30, 2009
I’m looking for a formula that will give me a figure against the name in column A (name) while looking picking out a specific word within the column B, say "alarm"
So it reads
Alan Ingram 1
David walker 3
And so on...
View 9 Replies
View Related
Sep 3, 2008
I have undertaken a project and have hit a brick wall.
I'll try and translate my request in the easiest way I can.
I have a piece of software that stores what software is installed on a machine when it connects to my domain.
I therefore have a list of software that is installed on machines that connect to my domain.
Each machine that connects to the domain has a unique identifier code.
At present I have about 500 different pieces of software on the various machines.
The unique identifier for the software installed on each machine is a combination of the application (column B), the program (column C) and the version (column D)
I wanted to give each seperate piece of software a unique ID number.
I was wanting to run a formula similar to the pseudocode below:
If $b$1="adobe" and $c$1="8.0.0.1" and $d$1="adobe reader", then "1",
elseif $b$1="adobe" and $c$1="7.0.0.1 and $d$1="adobe pro" then "2", else "no software ID"
I am thinking that if I can get every piece of software that is installed on the machines to have a software ID then I can create a software ID allowed list then I can create a formula to identify what laptops have software on the "allowed" and "banned" lists.
The problem at present is getting the sotfware to lookup what software ID they have as they need to check 3 columns with a lookup list.
View 9 Replies
View Related
Feb 11, 2013
I have a sheet that begining in A3 and going down need to look for the First Instance of the text String "Loan Documents" and down to the last instance and select the corresponding range in column B.
Example: if the Text String appears in Range(A14:A32) I need the Range(B14:B32) to be my selected range.
View 1 Replies
View Related
Aug 24, 2009
Here is my spreadsheet
A B C D 1 Matt 5 4 75 2 Joe 25 23 66 3 Ron 31 33 82 4 Jim 22 45 91 5 Steve 99 100 11
I used the large function to find the top 5 greatest numbers. I have them ranked in another area below the first set of numbers.
100
99
91
82
75
I need to find some sort of formula to return the name for the appropriate number. For example, I need the cell immediately to the right of 100 to return Steve; 99 to say Steve; 91 to say Jim. The INDEX/MATCH formulas used for left lookups works only for a column. I would have to set the index/match formula individually for each column to the right of the top 5 numbers, and that defeats the purpose. I might as well just find the numbers and connect the names all by hand.
View 9 Replies
View Related
Nov 4, 2009
I have a spreadsheet with a list of thousands of products in Column A. In Column B, C, D, E, & F there are headers that read: Cincinnati, Dayton, Columbus, Toledo, Cleveland.
In the rows under these column headers it’s either blank or there is a 1. The value 1 represents usage of products (column a) in those markets (columns b thru f). I guess I could use a Y for yes but I used a 1 instead.
In another workbook, I have a short list of a dozen random products that I am trying to match up to the cities that use them. Is there a simple formula that would search for a specific product in column a & return the first market with the value 1 in the corresponding cell?
View 9 Replies
View Related
Jan 8, 2009
I would like to do a vlookup where the lookup value isn't in the first column of a range/table. Would I use index/match? For example, I have fields for vendor, part # and location in that order. I'd like to pull up location with a formula based on part #.
View 4 Replies
View Related
Jan 16, 2014
I have a table (A1:C5)
A1:C1 contain the table headers
A2:C5 contain the data
A6 contains the value I want to search for I use this formula to return the column header of A6 A6=INDEX(A1:C1,SUMPRODUCT((A2:C5=A6)*COLUMN(A2:C5))) Now, if there are duplicate values in A2:C5, this will return more than one column header What I need is a vba code to view a msgbox with all column headers returned in this case
View 1 Replies
View Related
Jul 26, 2013
I have a column of numeric values in column B. I have the date that corresponds with each value in column A. I am using a max formula to find the highest value in column B. What could I use to return the date of the highest value from Column A?
View 2 Replies
View Related
Jun 8, 2006
i need a formula that tells me if a value exists in a column. for example if there is a value in a cell in the b column of 23 no matter where it is, i want cell c25 to return the word "yes".
View 2 Replies
View Related
May 7, 2014
I need a lookup formula that matches first three digits of column, U (153 - LINE PIPE), and match Total 13153 - LINE PIPE, 7th, 8th, and 9th digit in column C. and bring value from either column T,
The formula that I am using is not obviously not correct, I am putting it here to show what I am trying to do.
{=INDEX($T$2:$T$16,MATCH(LEFT(S2,3),MID(C2:C273,FIND(" ",C2),3)),FALSE)}
View 5 Replies
View Related
May 16, 2013
I'm doing a statistics workbook there I need to add untill the given value in the cell
ex: if gave 5 in the cell we want to get the total until only to 5th cells
(M/M/S)FIFS/INF/N)
No of Server
3
l
0.20
n
Coeffient
P(n)
[code]....
We changing the value in N=5
View 7 Replies
View Related
Apr 2, 2009
I want to create a series of VLOOKUP commands with different lookup tables whose names are concatenated from two different pieces. Here's what I'm trying to do:
VLOOKUP(x,LUTWK01,y,false)
VLOOKUP(x,LUTWK02,y,false)
VLOOKUP(x,LUTWK03,y,false)
LUTWK01, etc., are named ranges. I have the text "WK01", "WK02", "WK03" stored in other places, and would like to be able to concatenate "LUT" and the particular week (e.g., "WK01"). The formula that I've created, which isn't working is:
VLOOKUP(x,CONCATENATE("LUT",A1),y, false) where cell A1=WK01.
When I pull out just the CONCATENATE portion, it resolves to LUTWK01, but apparently this is not recognized as a range name. Any idea as to how I make the VLOOKUP formula recognize a concatenated range name? Or is there a better way to do this?
View 2 Replies
View Related
Apr 9, 2009
As I copy and past my lookup formula down the page it is changing the lookup range which I think is what is giving me so many #N/A results. My first Formula is
=IFERROR(VLOOKUP(A2,Coors2!A2:D3765,3,FALSE),IFERROR(VLOOKUP(A2,'AB2'!A2:C13944,3,FALSE),(VLOOKUP(A2 ,'WM2'!A2:C4843,3,FALSE))))
Then for instance at line 59 the formula is
=IFERROR(VLOOKUP(A59,Coors2!A59:D3822,3,FALSE),IFERROR(VLOOKUP(A59,'AB2'!A59:C14001,3,FALSE),(VLOOKU P(A59,'WM2'!A59:C4900,3,FALSE))))
View 2 Replies
View Related
Jan 30, 2014
I'm trying to create a macro that can lookup values down a list, find that value within another list and copy the adjacent cells.
The values to look for will always be on the same column and the values to look into will always be in the same range or columns but not always in the same row.
For example.
AI:AI contains a list of ID's which will be manually input every day so they might have a different order.
C:AF has all the data to look into. C always being a list of ID values and I want for every ID found on AI:AI to be search for on C:C: and then once a match is found, I need specific adjancent cell values to be copied next to the ID found on AI.
The values that need to be returned are found on columns B,D,F,G,H,I,J,P,AF and they will always display on those columns.
I've been doing this based on vlookup and INDEX/MATCh formulas but I've come across some limitations for something I want to develop further so I'm trying to do this based on VBA.
View 5 Replies
View Related
Apr 29, 2009
I would like to write a formula to have a result in a cell that gives me the maximum value of a column, but smaller than another cell.
For example:
C1 = Maximum value in column A1:A20, but smaller than B1.
View 14 Replies
View Related
Jul 8, 2014
I have around 500 csv files, each with four columns of data. However, the first column does not have a heading. Is there any way I can give the column name to the first column in each of 500 csv files? Here is one of the csv files:
name1 name2 name3
1774.77 4.15 29.28
17810.45 6.2450.36
18257.86 16.9169.11
18326.46 20.1 18.93
1841.48 20.29 0.95
1850.84 20.45 0.79
2031.49 3.44 8.86
2040.06 3.41 -0.87
2050.02 3.37 -1.17
2060.91 3.51 4.15
20711.35 2.77 -21.08
2085.23 3.51 26.71
2090.02 3.51 0
2100 3.5 -0.28
2110.06 3.33 -4.86
21229.25 6.91 107.51
View 8 Replies
View Related
Mar 8, 2012
I am currently using the .resize function to give a specific value to each cell within a range.
First of all, is it the most efficient way to do it?
If so, why am I getting error "Application-defined or object-defined error"
with this code :
Code:
ThisWorkbook.Sheets("Merge").Range(Cells(3, 2), Cells(4000, 2)).Resize.Value = "TEST"
View 2 Replies
View Related
Sep 3, 2009
FromToTriggerrange26Stop-lossrange25Combination triggerlevelie: 4Combination stop levelie: 5Profit $10
How can I make "Solver" run a check on which combination of trigger and stoploss that gives the highest profit? The range is in two different cells, but I can't get it working. I guess that it's my lack of skills, and not "Solver" that isn't complex enough.
View 9 Replies
View Related
Sep 27, 2011
I have a query on retrieving the result from a set of range from other sheet.
Query: In the Sheet1 Column B has to take first two words from the
Column A & lookup the matching results from Sheet 2.
(The search range should be the first two words of each cell)
Result: The complete sentance in the Cells (First Two words) should get placed in the Sheet1 Column B
Example: The Expected result should be as mentioned below. (Blue in color should be the results)
Sheet1A BC1NameResults2abc def fkfeabc def words3ghi jkl kikgN/A4mno pqr stu lkasmno pqr must work5
Don't give my book don't give up6vwx yzyvwx yzy not7xwv uts rqpxwv uts yes8omn lkjN/A9ihg fed cbaN/A
Sheet2A BC1Lookup Data2abc def words3xxxghi jkl4yes you have to5don't give up6no you will not7mno pqr must work8vwx yzy not9Excel work10xwv uts yes11omn mre lkj12ihg not fed 13not work14
This is my query
View 7 Replies
View Related
Jan 8, 2009
I have a lookup tab which contains three columns: Column A is a tax ID #, Column B is a set of numbers and Column C is a list of street names. I need help with creating a lookup type formula for my column AB in the (worksheet/tab) titled 2009, which will look at and compare the data in columns F & G and compare them to columns B & C in my "lookup" tab and if a similar address is found based on both the street name and number then the tax ID # affiliated with that address from the "lookup tab" will be automatically placed in the column AB in the 2009 tab. I hope this makes sense.
View 4 Replies
View Related
Feb 26, 2012
I'm trying to find a formula that will find an original entry using 4 criteria original entry is cases ordered.
columns A,B,C,D will have to match then give me a result in column E to find original entry if there is one otherwise I will have to enter new row and original entry for cases ordered.
Eg.
A = section
B = description
C = size
D = region
E = cases ordered
Row 2 = fruit, apples, medium, north, 25
Row 45 = fruit, apples, medium, north,
View 3 Replies
View Related
Nov 27, 2007
When using the formula '=COLUMN()' in cell A1, it returns the number of the column - in this case, '1' (for column A). Is it possible to affect this formula so that it returns the column letter (in this case, 'A')?
View 9 Replies
View Related
Jun 18, 2014
I have a list of sites and depending which site I select from my drop down box I want to retrieve that data from my full data sheet. How can I go about doing this?
View 3 Replies
View Related
Oct 27, 2005
The following formula shoud give an answer of 178. I think I am placing the
parenthesis in the wrong position.
The answer I get is 308669
=(((F22*71449)*0.4)+16147/C22)
Cell-F22 number is 1826
71449 is a constant number
0.4 is a constant number
16147 is a constant number
Cell-C22 is 10.8
View 10 Replies
View Related
Apr 8, 2009
I have 6 rows (A-F) with dollar vallues. I am trying to create a formula on row G that will give me the highest dollar value out of rows A-F.
View 9 Replies
View Related
Jun 8, 2007
I am trying to do something for a baseball game I am creating. What I am looking for is a formula for one cell that will basically give me a whole number when a certain criteria is met. I am also looking for this cell's formula to get part of it's formula from another cell in the document. So let's use cell A1 has 1.50 in it.
I want this other cells formula to be able to give me a 9 if A1 is 0.00-1.50, a 8 if A1 is 1.51-2.79, 7 if A1 is 2.80-4.30, a 6 if A1 is 4.31-5.49 and 5 if A1 is 5.50-500.00.
View 6 Replies
View Related
Sep 18, 2006
Attached is a spreadsheet that contains a list of Football teams (F) with corrasponding numbers next to them (E). In column A is a list of footballers and column B has a corrasponding number. WHat I want to be able to do is type in the footballers names in A and type a number next to them in B, in column C it would read off the corrasponding Team number and populate it with the team name. In the attached example I'll manually list the fist teams in B, i need a formula todo this for me. Probably quite simple.
View 2 Replies
View Related
