Search For Cell Value Through A Range

Mar 2, 2007

Is there a formula that will look through a certain range of cells for a specific value which is held in another cell.
So if P21 holds the number 21, search through P17:S19.
if the number (in this example 21) is found in that range place W in P23 if it isn't then L in P23

View 9 Replies


ADVERTISEMENT

Search Range In One Sheet - If Any Cell In Range Is Greater Then 0 Copy That Row

Sep 24, 2013

Basically I have three sheets. MAIN, Sheet 1 and Sheet 2

Sheet 1 and 2 are in the same format

A3 down is a list of country names and then B3:I71 contains the data im interested in.

I've been trying to create a function that looks at B3:i71 to see if any cell in that range contains a value greater then $0.00. If it does then the row that contains the cell with a value greater then $0.00 (between col A to K) should be copied to sheet MAIN from cells B3 down. This should ultimatley produce a list of data for any row containing a value greater then $0.00. This process should then be repeated on Sheet 2 and should join the list below sheet 1.

View 3 Replies View Related

More Than 1 Cell As Search Range: Result Is Wrong

Apr 6, 2009

i've been using an IF function in order to display a certain value when one or more criteria are met in one cell in one specific worksheet. here's how it looks like:

=IF(OR(ISNUMBER(SEARCH("value1",Worksheet1!A1)),ISNUMBER(SEARCH("Value2",Worksheet1!A1))),"yes","no" )

with different values and names.. but it's just to give you the idea of what kind of function it is. this one works perfectly, but i also needed another one in which the logical test range is no longer only one cell, but more, thus a selection. i wrote the same formula, but instead of the "A1" i wrote for example "A1:A20" which i thought it would work.. but it doesn't. even more surprising, when i open the insert function (the helping wizard that pops up when i click the "fx" button on the left of the function string) and i put in the logical test, at the bottom of the window it says the correct result (ie: yes). but on the actual worksheet, the cell displays the wrong result (no). i know there are no errors in the formula, so i really don't know how to solve the problem.

View 10 Replies View Related

IF Statement To Search If Value In Cell Is Equal To A Value In Range

Jul 2, 2014

I want to create a simple if statement to search if a value in cell B2 is equal to a value in the range 'CodeCheck'!A1:A92

I have written the code =IF(B2='CODECHECK'!A1:A92,"Y","N")

I am always getting N returned even though I am 100% sure the data in B2 is in the 'CODECHECK'!A1:A92 range.

View 5 Replies View Related

VLookup To Search Range On Different Sheet And Return Appropriate Corresponding Cell

Jan 22, 2014

I need to lookup to search a range on a different sheet and return the appropriate corresponding cell.

Basically its if a1 is found in the other sheets range a1:a100 then return the corresponding Bcolumn value from the different sheet.

Formula
=VLOOKUP(A2,'All Users'!A$2:A$100,'All Users'!B!2:B!100)

Example of 'All Users' Sheet

A B
STAFFIDSTAFFNAME
24177John
10487Paul
20031George
84087Ringo
85772Pete
24485Stuart
3829George
51836Yoko

View 3 Replies View Related

Search A Range To Enter Text In Adjacent Cell

Jul 19, 2006

When i press say a command button " Add Repair Information" i want the User to Enter the serial number which i have given it a range called SerialN from the excel sheet and enter a repair action. Is there a function in Excel which will allow me to search the Serial Number and allow me to put the Repair action across in the cell on the same line?

View 5 Replies View Related

Search For Last Cell In Range Containing Data - Get Value From Cell Above?

Apr 19, 2014

I need to find the last cell in range AM3:BD3 with data (text or number) and get the value from the cell above.So if the last cell with data in range AM3:BD3 is AP3, get value from cell AP2.

View 7 Replies View Related

Lookup Nth Occurence In Range & Limit Rows To Search Based On Cell Value

Jan 14, 2009

I have a UDF for a lookup_occurrence formula (thanks to Dave), and I was wondering if it was possible to tweak the range it looks at with if statements.

I have a formula like this =Lookup_Occurence(B13, INDIRECT($N$5), 1,1,5) where N5 says exit card 1.

I need it to only look at a certain rows within exit card 1 depending on the value entered in I5.

If value I5 says Period_1 it will only look in rows 12:46.

If value I5 says Period_2 it will only look in rows 48:82 etc.

I believe I need to modify the xl look part of the code with if statements.

Below is the code. I am wondering if I can replace the xlLook line with nested if statements. If I5="Period_1 then rows 12:46" etc.

Function Lookup_Occurence(To_find, Table_array As Range, _
Look_in_col As Long, Offset_col, Occurrence As Long, _
Optional Case_sensitive As Boolean, Optional Part_cell_match As Boolean)

Dim lLoop As Long
Dim rFound As Range
Dim xlLook As XlLookAt
Dim lOcCheck As Long

View 5 Replies View Related

Sumproduct? Sumif? (search A Range Any Cell Containing Words Or Phrase "20%" )

Jun 4, 2009

I am very comfortable with SUMIF, but need to search a range any any cell containing words or phrase "20%" then need to sum those corresponding cells only.

I.e., something like: SUMIF(A1:A10,..."20%",C1:C10) .... only sum all C cells where an A cell contains any combination of words with "20%" in them.

View 2 Replies View Related

Search Large Date Range With Narrow Filtered Range

Aug 1, 2014

I need to be able to query a large date range by a small beginning and end date range and return a count when the value is = each search criteria. i.e. - Search one year of dates from a table by Beg: 7/23/2012 to End: 10/21/2012 and return a count. The beginning and end dates are dynamic and I will need to reference the cells, i.e. B102 "Beg" B102 "End" and not a static date.

View 7 Replies View Related

Search Cell Value In Another Workbook And Copy Range From Current To Other Workbook

Mar 11, 2014

I have 2 workbooks. 1 is where data is entered called wksPB, the other is like a database workbook. The wksPB has data to be feed from column B to F and there is a dropdown.

what im looking to do is, if the combobox value is selected as Decline then it should display a messagebox that reference value in cell B is denied. if combobox value is Agreed then the macro should search the database workbook for the text entered in column b of wksPB and then copy data of C:E of wksPB to H:J of database sheet where that text is found and also the approver name in wksPB C24 to the approver cell of where that text was found. I've reached till finding the text but what I get my head twiting on is how to copy the text from wksPB column B:F to database sheet column H:K. Im attaching the sample workbooks and the code where ive reached till.

[Code] .....

Attached Files
Replacement Records - 2014.xlsx‎
forum file.xlsm‎

View 1 Replies View Related

Search A Range Of Cells For A Range Of Values

May 28, 2009

I'm trying to do is search a range of cells for a range of values AND then see if it matches one other value in another range of cells. In this case,

=SUMPRODUCT((T3:T49="P6")+(T3:T49="P5")+(T3:T49="P4")+(T3:T49="P3"))*(U3:U49="w")

T3:T49 can equal P6 or P5 or P4 or P3 but the cells can only be counted if U3:U49 is 'w' as well

View 3 Replies View Related

VBA Search Within A Given Range

Dec 23, 2008

I have the following ...

View 7 Replies View Related

Add Another Search Range

Feb 17, 2010

The intention of the below code is to write out the line is there is not a match in both ranges.

Instead of just D2 being searched, i need it to look into range E2 also if not found in D2.

At the moment if it is not found in D2 it is writing the line, though the match actually appears in E2 range in this instance, thus should not be writing the line.

FYI:
- Of course if there isnt a match in D2 range though there is in E3 then no line should be written as there is a match.
- if there are no matches in the 2 ranges then only 1 line should be written in the same fashion as currently coded.

View 6 Replies View Related

Search Range For Value

Apr 11, 2007

What's the best way to search every third row in a range ("rDRange") which is one column wide to see if there is a match to "sDept".

View 9 Replies View Related

Search From Textbox To Range

Jan 22, 2013

I have a userform with text boxes. When I click on the GO button on the user form I want it to search from the TextBox to the range of rows on sheet2 from start to end and if duplicate entry is found it should display a message box.

For E.g. After pressing GO button Textbox entry should search Sheet2 (where all the data is entered) from the range starting from B6 to last entry of cell and if duplicate entry is found a message box appears. This entry keeps on getting updated and so it may increase or decrease, and therefore VBA should be such that it searches to the last entry.

View 3 Replies View Related

Search For A Value In A Specific Range?

Dec 21, 2013

I want to search for a value, in a specific range (in this case, in a row). If that value exists, l want the formula to return that value, otherwise l want it to return "FALSE" or whatever.

The formula must exist in one discrete cell only, i.e. the output will be contained in only one cell.I don't want to match the occurrence of that value with another row's cell. I don't want to search for multiple values (although if you have a way to do that, l'll be interested to know!), l don't want to count the number of times the value occurs.All l want is to return that value if it exists in the specified range of cells, and if it doesn't, l want to say "FALSE" or something.

View 7 Replies View Related

VBA To Search With Range Of Cells?

Jun 27, 2014

i currently have the following code set up to look at one cell (named 'TypeSelect'). If that cell = "a" it unhides tab "TAB DETAIL A". If it doesnt = "a" the tab remains hidden. I now want this to search across a range of cells (B2:B7 or using named range 'Category_Select') for "a".

Ultimately the code is to look for any from a list of categories within a range and unhide the relevant tabs if the category is present.

View 1 Replies View Related

Search A Range In A Sheet

May 12, 2009

I have a monthly time sheet. The times are entered in cells C8:F68.
When some one has a annual leave day or a bank holiday they enter A/L or B/H.

I have a summary sheet and have placed a cmd button on it to hopefully search the sheets that are months or the year.
I know how to search the sheets and increment through them and not the 'Summary' sheet.

What I need to establish is how to place in my code the actual search criteria and also limit the range to C8:F68
On top of this I need to total each occurance of A/L and B/H and display them in two cells.

I have tried to do a search of the forum but with no luck.
Like I say I have managed to understand how not to select the 'Summary' sheet when incrementing through the sheets but its just the search for the A/L and adding them up!

View 12 Replies View Related

Can ISTEXT Search A Range

Jul 5, 2007

I'm currently designing a new spreadsheet and I can't change the formula I'm using below to search more than one cell or a range. Is either choices a option?

I'm using =IF(ISTEXT(E135),G135) Can I add cell E139 (and how many additional cells could be added) or can I set it up to search E135:E144.

View 13 Replies View Related

Search For Number In A Range

Mar 29, 2012

I want to search for contents of A1 in range of A1:B3 and if it is in the range return Y or N if not there.

View 9 Replies View Related

Search A Range For Specific Value

May 3, 2014

I need to look through a range for a specific value. When I find it if the nearby time column matches a specific time I want to retrieve the lookup value. Because I am not very macro savvy, and the spreadsheet has to run on both mac and pc I do not want to use a macro. Can I do this with an IF((AND combination or is there a better way? If there is a way to look through a range with IF, how would I structure the criteria to look through an entire column for the value? Lookup won't work because the value can occur several times through out the range, but only once with a specific time value.

View 5 Replies View Related

Column Range Search

Oct 24, 2008

If a column contains it contains apple and ipod then display manufacturer as Apple Corp in c2. How can i do this?

View 9 Replies View Related

Search For Value That Falls Within A Range

Oct 13, 2009

Example:

0000-0999 Cell a1
1001-1999 Cell a2
2000-2999 Cell a3
3000-3999 Cell a4

These are a ranges of values in a spreadsheet. I would like to be able to do a search or lookup for a number that I designate that falls within one of these ranges and it will direct me to the cell that my value falls within.

For example if I do a search for 1550 it would take me to cell a2 with a range of 1001-1999.

Not sure if this is too complicated for a formula and might need to use VBA.

View 9 Replies View Related

Search For 2 Values In A Range

Jan 22, 2010

Is it possible to use the match or an other excel function to search for the first occurence of one of two values in a range?

For example in the below range i want to find the location (number like the match function) of the first occurence of "No" or "Closed":
Project Name Tasks DocumentTask ReviewTask ApprovalIssue DocumentIssue ReviewIssue ApprovalMilestone DocumentMilestone ReviewMilestone ApprovalPro 1closedYesyesyesyesclosedYesyesno

View 9 Replies View Related

Search Specified Range And Hide Zero Values

Feb 8, 2012

I am familiar with the custom formatting option to hide cell contents when you don't want them displayed in either a chart or when printing: Custom > Type > ;;

I was trying to create a macro that will search a specified range and whenever there is either a zero or nothing displayed in the cell (even though a formula exists), I want to custom format the cells to not be displayed.

My range is Range("B5:I16")

I tried to create something like

Code:
Dim dataRange as Range
Set dataRange = Range("B5:I16")
For each Cell in DataRange
if Cell ="" then Selection.NumberFormat = ";;"

I'm struggling to figure out how to tell Excel to step through each cell in this range, evalaute whether there is a blank and if so, apply the NumberFormat ";;" to it, otherwise let it be.

View 3 Replies View Related

Vlookup: Search A Certain Range Of Cells

Apr 3, 2008

1. can i limit vlookup on sheet2 to search only a certain range of cells on a sheet1.

example, I want vlookup to look in sheet1, colum A - Y but only look between rows 1 and 100

2. If 1 can be done, and i happen to add a row in worksheet1 (making it 101 rows vice 100) will the vlookup code on sheet2 include that 101st row or do i have to redo the range manualy.

View 9 Replies View Related

Search Range For Part Strings

Aug 21, 2008

I am using the following code to search a database of information and then display it on a different sheet.

The user types the search term into a textbox and then presses a command button to search the database.

Currently it only searches for an exact match. How can i adapt it so it searches for similar strings?

Sub SearchDatabase()

Dim rRange As Range
Dim rCell As Range
Dim ResultsOffset As Integer
Dim ResultsRange As Range

On Error Resume Next

View 9 Replies View Related

Using VBA To Search Range And Register Row Number Of First Occurrence Of Each Value

Sep 25, 2013

how to search a defined range (using namned range and offset to last non empty row) and register row number for the first occurance of each ID using VBA. I made an example file where I need to search column A (search range using namned range "ID" and offset to last non empty row) and register rownumber for the first occurance of each ID in Column C.

example.xlsm

View 1 Replies View Related

Search Range Of Cells For Text And Return Value Below It - Sum If More

May 12, 2014

I want to search within cells A4:F25 for "B71 - Brown" and show the number that is in the cell below this text. If there is multiple cells with "B71 - Brown" I would like the cell to show the total.

So for example,If the formula was looking for "B71 - Brown" in the cell it would show 8136. (A13+A21+D25+E17). If i changed it to "Lavender" it would show 2380. (C5)

Also this data is linked from several other sheets and the values i search for will change and will need to update.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved