Vlookup,Index,or Combo of something else

[D]

I have 2 worksheets. On Sheet1, if Column H value ="M", then on Sheet2:A2,
bring over the value of Sheet1:A2. If it doesn't find the value "M", then
search for the next row that does.

 

[David McRitchie]

I have 2 worksheets. On Sheet1, if Column H value ="M", then on Sheet2:A2,
bring over the value of Sheet1:A2. If it doesn't find the value "M", then
search for the next row that does.

See http://www.mvps.org/dmcritchie/excel/vlookup.htm

H4: M
I4: =VLOOKUP(H4, sheet2!$A$2:$B$50, 2, 0)

You can improve the above to avoid #N/A errorl with the following:
=IF(ISERROR(VLOOKUP(H4,sheetb!$A$2:$B$50,2,0)),"not found",VLOOKUP(H4,sheetb!$A$2:$B$50,2,0))

The 0 is False, meaning the list is unsorted.

lookup_value,table_array,col_index_num,range_lookup)

 

[D]

Let me try to explain again:

I want the value of Sheet1!A2 to be transferred onto Sheet2!A2 if the value
on Sheet1!H2 ="M". By doing a straight lookup, if my value in Column H on
Sheet1 is anything other than "M" it will pull a blank. I do not want these
blanks on my Sheet2.

 

[David McRitchie]

I had them backwards used sheetb for sheet1, and sheeta for sheetb
but it seems more like you want to use a filter, in which case you
only need one sheet.

If you are trying to display only rows in sheet2 that have an "M"
in Column H then you would filter on Column H for a value of "M"

Select column H then
pref Excel 2007 Data, Filter, on the drop down on row 1 choose "H"

in Excel 2007 (forget it but it is)
Data, Filter, on the drop down on row 1 choose "H"

So much for that, can't stand Excel 2007

You can copy a filtered list and paste is elsewhere as if
the filtered out (unshown) columns aren't even there.
Same for using a filtered list for Mail Merge in MS Word
using a filtered list in Excel as the database.

Debra Dalgleish has pages on Filtering, is that is the type
of thing you want. http://www.contextures.com/tiptech.html

 

[D]

This process needs to be automatic therefore a formula needs to be in place.

 

[David McRitchie]

Well was it filtering that you wanted?
If so you can work from there.

A formula can only get data;
However, you can use a formula in a Filter
as seen on Debra's pages.

If you want VBA coding then see
http://www.contextures.com/xlautofilter03.html