Detect strings in a range
 

I have a duty rota consisting of a range of cells containing names. The first
column of the range contains dates. The column headings are the duties.
I want to present this information in a different way for the benefit of
those on duty. I want to construct a new table, ideally on a different
worksheet, containing a list of names down column 1, the duties across the
column headings in row 1, and dates in the rows. I want the people to be able
to look at this table to find out quickly which dates they are responsible
for.
I began by trying to use VLOOKUP until I foiund that this requires an
alphabetically-sorted table.
I can explain the function like this:
I want to match a string within a range of cells.
If true, I want to print the contents of the row in the date column.
If false, I want a blank cell.

Detect strings in a range
 

You can use vlookup

however use vlookup(val,range,col,False)

the false looks for an exact match and does not require an ordered list

Lance

"Rich57" wrote:

I have a duty rota consisting of a range of cells containing names. The first
column of the range contains dates. The column headings are the duties.
I want to present this information in a different way for the benefit of
those on duty. I want to construct a new table, ideally on a different
worksheet, containing a list of names down column 1, the duties across the
column headings in row 1, and dates in the rows. I want the people to be able
to look at this table to find out quickly which dates they are responsible
for.
I began by trying to use VLOOKUP until I foiund that this requires an
alphabetically-sorted table.
I can explain the function like this:
I want to match a string within a range of cells.
If true, I want to print the contents of the row in the date column.
If false, I want a blank cell.

Detect strings in a range
 

A VLOOKUP table does not have to be sorted alphabetically if you use the fourth
argument of FALSE which will return #N/A if string not found

=VLOOKUP(cellref,TableRange,tablecol,FALSE)

Assume your table is A1:B10 and you enter the string in C1

in D1 enter =VLOOKUP(C1,$A$1:$B$10,2,FALSE)

To return a blank if false use an error trap.

=IF(ISNA(VLOOKUP(C1,$A$1:$B$10,2,FALSE)),"",VLOOKU P(C1,$A$1:$B$10,2,FALSE))

On another sheet

=IF(ISNA(VLOOKUP(C1,Sheet1!$A$1:$B$10,2,FALSE)),"" ,VLOOKUP(C1,Sheet1!$A$1:$B$10,2,FALSE))

Less typing if you designate a named range for the Sheet1!$A$1:$B$10

Select that range on Sheet1 and InsertNameDefine

Give it a name like MyTable

Then formula becomes........

=IF(ISNA(VLOOKUP(C1,MyTable,2,FALSE)),"",VLOOKUP(C 1,MyTable,2,FALSE))


Gord Dibben MS Excel MVP

On Sun, 30 Dec 2007 14:52:01 -0800, Rich57
wrote:

I have a duty rota consisting of a range of cells containing names. The first
column of the range contains dates. The column headings are the duties.
I want to present this information in a different way for the benefit of
those on duty. I want to construct a new table, ideally on a different
worksheet, containing a list of names down column 1, the duties across the
column headings in row 1, and dates in the rows. I want the people to be able
to look at this table to find out quickly which dates they are responsible
for.
I began by trying to use VLOOKUP until I foiund that this requires an
alphabetically-sorted table.
I can explain the function like this:
I want to match a string within a range of cells.
If true, I want to print the contents of the row in the date column.
If false, I want a blank cell.

Detect strings in a range
 

Thanks for that, Gord, and for the FALSE reminder, LanceB.

I just have one issue: the 3rd argument in VLOOKUP is a column number. I
have the formula on a separate worksheet (called Dates), and the result takes
the data from col 1 on the Dates sheet instead of where the cell range is.
This argument is a number so presumably I can't use a cell reference here?
This gives a #VALUE! error. How do I get round this?

Richard

"Gord Dibben" wrote:

=IF(ISNA(VLOOKUP(C1,MyTable,2,FALSE)),"",VLOOKUP(C 1,MyTable,2,FALSE))


Gord Dibben MS Excel MVP

On Sun, 30 Dec 2007 14:52:01 -0800, Rich57
wrote:

I have a duty rota consisting of a range of cells containing names. The first
column of the range contains dates. The column headings are the duties.
I want to present this information in a different way for the benefit of
those on duty. I want to construct a new table, ideally on a different
worksheet, containing a list of names down column 1, the duties across the
column headings in row 1, and dates in the rows. I want the people to be able
to look at this table to find out quickly which dates they are responsible
for.
I began by trying to use VLOOKUP until I foiund that this requires an
alphabetically-sorted table.
I can explain the function like this:
I want to match a string within a range of cells.
If true, I want to print the contents of the row in the date column.
If false, I want a blank cell.

Detect strings in a range
 

On your source sheet you would need at least a 2 column lookup table.

i.e. A1:B10

Your lookup cell(C1) on Dates sheet is the number to look up in Column A of
source sheet and return the value of corresponding cell in column B.


Gord

On Mon, 31 Dec 2007 03:14:00 -0800, Rich57
wrote:

Thanks for that, Gord, and for the FALSE reminder, LanceB.

I just have one issue: the 3rd argument in VLOOKUP is a column number. I
have the formula on a separate worksheet (called Dates), and the result takes
the data from col 1 on the Dates sheet instead of where the cell range is.
This argument is a number so presumably I can't use a cell reference here?
This gives a #VALUE! error. How do I get round this?

Richard

"Gord Dibben" wrote:

=IF(ISNA(VLOOKUP(C1,MyTable,2,FALSE)),"",VLOOKUP(C 1,MyTable,2,FALSE))


Gord Dibben MS Excel MVP

On Sun, 30 Dec 2007 14:52:01 -0800, Rich57
wrote:

I have a duty rota consisting of a range of cells containing names. The first
column of the range contains dates. The column headings are the duties.
I want to present this information in a different way for the benefit of
those on duty. I want to construct a new table, ideally on a different
worksheet, containing a list of names down column 1, the duties across the
column headings in row 1, and dates in the rows. I want the people to be able
to look at this table to find out quickly which dates they are responsible
for.
I began by trying to use VLOOKUP until I foiund that this requires an
alphabetically-sorted table.
I can explain the function like this:
I want to match a string within a range of cells.
If true, I want to print the contents of the row in the date column.
If false, I want a blank cell.