You are here: HomeExcelTutorials

# Finding a value within two different ranges

## Finding a value within two different ranges

Price: FREE

I am having difficulty getting a formula to extract a value from two different ranges. My data can only be pulled this way. Here is hat I am trying to do. I have named two ranges. Week (Column A to D) and Tech (column G to J) Each Column represents a week out of 4 weeks. In another sheet I have one value (Week number- column B). What I want the formula to lookup if A is "On-Call" and then lookup up B and search every name in my 'Tech" range that matches the week number from range "Week". It is difficult to explain. I have attached a spreadsheet. The formula result I want is in column F. (I highlighted in sheet Data the cells I want the results from.)

Visit publisher's web-site: Finding a value within two different ranges

Category and Tags: ExcelTutorials :

## Related Tutorials

### Finding Time Ranges Given a Criteria

Free: Yes
Tags: Excel ›

I need a formula that will do the following. I have a report that shows time intervals in column A and number of calls in column B. I need the start time and end time of the ranges of time where the calls exceed 30. So for the spreadsheet that I attached I would need the formulas to return 8:30 AM-11:00 AM, 12:30 PM-2:30 PM, and 3:30 PM-5:00 PM.

### Finding a Variable Value with in multiple ranges

Free: Yes
Tags: Excel ›

I have a table and I'm having the hardest time trying to find a function that will allow me to enter in a value and have it return the points listed. So for example if I type in .25 in a cell I would like it to return 100. or if I typed 1.30 it should return 250.
I have tried VLookup but I obviously am not doing it correctly if that is the function that I need to use. Thanks for any help that I receive.

### Finding duplicates in distinct ranges...

Free: Yes
Tags: Excel › Functions

I have like 300 columns with 50 values in each and I want to find duplicates across these groups - e.g. number 23,5366 appears in group no. 23, in group no. 45, in group no. 145 twice, etc. - and I need to get back the row reference of these duplicates ( conditional highlighting would suffice). How can I achieve this?

### COUNTIF not equal to using ranges

Free: Yes
Tags: Excel ›

I am having trouble trying to count and add up the number of cells that do not match between ranges. This is what I have right now:
=SUMPRODUCT(COUNTIF(\$R\$23:\$S\$32,"<>B2:E6"))
Of course, the problem I am having is when I put the "" in, it turns my B2:E6 range into text. I am sure there is an easy solution to this (I hope) but I am having a tough time finding any info on it.

### Finding End Week Dates Using Vlookups Within Date Ranges

Free: Yes
Tags: Excel ›

I'm trying to find the week end date using Fridays as the end of the week from a list that has the actual date work was done for items 1-12. The dates need to be with in the range specified at the top. Basically if the actual dates for work done for items 1-12 from the date table below fall into the specified range, the Friday end date of that week the work was done would appear in the table above and everything outside the range would remain blank. I've attached an very basic example. I imagine I would need to use some sort of Vlookup that looked at the table below to match the item number to the date and then if that date fell into the data range it would change the date to the Friday of that week.

## Related Applications & Scripts

### Analog Clock with Frame

Free: No
Tags: Flash › Excel Clocks

This clock is taking the time off your local computer and displaying it on the web page through Flash. This is not perfect as I don't know if your clock is correct. There are other ways of finding out the time but they are much more complicated and not necessarily more accurate. So I have gone for a very simple method of finding out the time. It works and is not too difficult to create.

### Filter Large Select Lists

Free: Yes
Tags: JavaScript › Excel Forms

If you have a very large HTML SELECT list, finding the correct item can be like looking for a needle in a haystack. For example, if you have a list of a couple hundred names, finding a single one can be difficult. This article provides a JavaScript technique for filtering the items in a select list so that only the matching items are shown, making it much easier to locate the desired item.

Free: No

Random-loading graphics to amuse your viewers. Easily select which graphic or even the colors to use by simply indicating in the code. You can also tweak the random scale & rotate ranges. Fullscreen ready. Aligns to center stage.