Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 14th 08, 11:44 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 38
Default Need a function to return EXACT row number of a match

Can someone help me with a formula that returns the exact row number
(i.e. the row number on the side of the spreadsheet) where a match is
found on? If possible, I DON'T want to use 'Match + offset number.
For example:

A B

480 bull turtle
481 Pig mouse
482 cat dog


I would like to use something like =MATCH("Pig",$A$480:$B$482,0). Of
course this will return a value of '2' but I want it to return a value
of '481'. I need to be able to specify the area to search in because
there are other rows that have the value "Pig" in it.

TIA

  #2   Report Post  
Old November 15th 08, 12:02 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2008
Posts: 411
Default Need a function to return EXACT row number of a match

It sounds like you want vlookup
  #3   Report Post  
Old November 15th 08, 12:13 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 7,247
Default Need a function to return EXACT row number of a match

I don't see why an Base+Offset won't fit the bill. You can use
somthing like

=ROW(INDIRECT("C"&ROW(C6)+MATCH("bird",C6:C10,0)))

to find the row number within C6:C10 on which the word "bird" appears.
If it is a matter of defining different lookup regions for a MATCH or
V/HLOOKUP function, you might be able to use the INDIRECT function.

If cell B1 contains the text string C1120, you can use INDIRECT like

=VLOOKUP(123,INDIRECT(B1),2,FALSE)

to set the look up range based on the value of B1, in this case
setting the lookup range to C1120. INDIRECT isn't limited to a
simple one-cell indirection as shown above. You can build up any text
string using any method you like, pass it to INDIRECT, and get a valid
range reference that can be used anywhere a hard coded reference might
appear.

But to answer your immediate question... No, there is no way to get
the row number of some value without resorting to one sort of
Base+Offset calculation or another.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Fri, 14 Nov 2008 14:44:44 -0800 (PST), wrote:

Can someone help me with a formula that returns the exact row number
(i.e. the row number on the side of the spreadsheet) where a match is
found on? If possible, I DON'T want to use 'Match + offset number.
For example:

A B

480 bull turtle
481 Pig mouse
482 cat dog


I would like to use something like =MATCH("Pig",$A$480:$B$482,0). Of
course this will return a value of '2' but I want it to return a value
of '481'. I need to be able to specify the area to search in because
there are other rows that have the value "Pig" in it.

TIA

  #4   Report Post  
Old November 16th 08, 05:05 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 38
Default Need a function to return EXACT row number of a match

On Nov 14, 5:13*pm, Chip Pearson wrote:
I don't see why an Base+Offset won't fit the bill. You can use
somthing like

=ROW(INDIRECT("C"&ROW(C6)+MATCH("bird",C6:C10,0)))

to find the row number within C6:C10 on which the word "bird" appears.
If it is a matter of defining different lookup regions for a MATCH or
V/HLOOKUP function, you might be able to use the INDIRECT function.

If cell B1 contains the text string C1120, you can use INDIRECT like

=VLOOKUP(123,INDIRECT(B1),2,FALSE)

to set the look up range based on the value of B1, in this case
setting the lookup range to C1120. INDIRECT isn't limited to a
simple one-cell indirection as shown above. You can build up any text
string using any method you like, pass it to INDIRECT, and get a valid
range reference that can be used anywhere a hard coded reference might
appear.

But to answer your immediate question... No, there is no way to get
the row number of some value without resorting to one sort of
Base+Offset calculation or another.

Cordially,
Chip Pearson
Microsoft MVP
* * Excel Product Group
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)



On Fri, 14 Nov 2008 14:44:44 -0800 (PST), wrote:
Can someone help me with a formula that returns the exact row number
(i.e. the row number on the side of the spreadsheet) where a match is
found on? *If possible, I DON'T want to use 'Match + offset number.
For example:


* * * * * * A * * * * *B


480 * * bull * * * turtle
481 * * Pig * * * *mouse
482 * * cat * * * * dog


I would like to use something like =MATCH("Pig",$A$480:$B$482,0). *Of
course this will return a value of '2' but I want it to return a value
of '481'. *I need to be able to specify the area to search in because
there are other rows that have the value "Pig" in it.


TIA- Hide quoted text -


- Show quoted text -


Thanks for the solution! I didn't think to add the starting row
number of the range to the match value to get the match row. I
actually had to subtract 1 from it to get the correct number. I.E.
=ROW($C$482")+MATCH($C11,$C$482:$C$550,0)-1



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to get exact match with LOOKUP function? s51janez Excel Worksheet Functions 1 October 2nd 08 01:21 PM
Find Exact Match using INDEX, MATCH DoubleUU Excel Worksheet Functions 3 August 15th 08 02:42 PM
If function to match cols and return value of another col Haz Excel Discussion (Misc queries) 1 April 29th 08 03:43 PM
match a name and return a tel number? Al Excel Discussion (Misc queries) 3 October 25th 05 04:10 PM
MATCH function - 2 columns w/ SIMILAR, not EXACT data Jane Excel Worksheet Functions 3 March 5th 05 04:11 AM


All times are GMT +1. The time now is 07:40 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017