2009
07.21
07.21
D_Icon asked:
I have an Excel spreadsheet, version 2003. I have a column of data in column Q. I want to see if any of the cell values in column match the cell values in column H. If it does, I want Excel to return the location of that cell.
I have an Excel spreadsheet, version 2003. I have a column of data in column Q. I want to see if any of the cell values in column match the cell values in column H. If it does, I want Excel to return the location of that cell.
Anyone know how to do this?
Hector
Deborah
This is a problem which I would solve with a formula constructed with 3 steps: Assume cell to check for matches is in Q1 and data to check is in cells H1 through H100.
Step 1: Find if there is a match in column H.
=MATCH(Q1,$H$1:H$100,0) (returns #N/A if there is no match and returns the place within H1:H100 of the cell which matches Q1 if there is a match)
Step 2: Now “go to” that cell:
=INDEX($H1:H$100, MATCH(Q1,$H$1:H$100,0)) (Now the formula from step 1 is used as the index in the reference range.)
Step 3: Now grab the cell address of that cell:
=CELL(”address”, INDEX($H1:H$100, MATCH(Q1,$H$1:H$100,0)) )
gives the “A1″ type cell reference of the cell which matches Q1.
Please note that if there is no match, you must add the appropriate error identifiers;
For example, =IF(ISNA( formula ), “No Match”, formula)
Good Luck.
Mario
you can also use the vlookup function
create a new column next to column H and put in it the name of the location (H1, H2, H3 etc). If you do the first 3 and then double click the corner of the cell it will autofill all the way down to the end of your sheet.
do a vlookup function on column Q comparing it with Column H and pulling information from column I You can use the formula button to help you create the formula. I think it will look like this: =vlookup(Q1,H:I,2,false)
**please excuse as I don’t have excel on this computer**
you’re asking it to take the value in Q1, look in column H, and if it finds a match, return column I. (false means find an exact match, not approximate)