NAMING a row number... ?

[Jennifre]

Hi, I need to get criteria matched from one column to another cell on a worksheet, then to tell me what row the data is in that it is matching at the top of the worksheet (here, in column HE).

(I know this is absurd, but I will be moving the rows around up top, so I need to tie what I'm doing to this as a formula if possible.)

Super-dorky "example":

=ReportRowNumberOfMatchInHE(IF(DG$2:DG$1364=$ER86))

 

[Misca]

The MATCH -function tells you the row number if you start the range from row 1. So try something like:

=MATCH(ER86,DG1:DG1364,0)

More info on MATCH: http://www.techonthenet.com/excel/formulas/match.php

 

[Aladin Akyurek]

Hi, I need to get criteria matched from one column to another cell on a worksheet, then to tell me what row the data is in that it is matching at the top of the worksheet (here, in column HE).

(I know this is absurd, but I will be moving the rows around up top, so I need to tie what I'm doing to this as a formula if possible.)

Super-dorky "example":

=ReportRowNumberOfMatchInHE(IF(DG$2:DG$1364=$ER86))

=MATCH($ER86,DG$2:DG$1364,0)

would be the right thing to do...

 

[Jennifre]

Good morning, thank you Misca, Aladin!

These didn't work for me, so I am sure I am doing something wrong.

In the formulas you sent me, I get 155 if using DG2, and 156 if using DG1.

The row where 1211 is residing up top on this worksheet is 17 in case that's useful.

In case there's another way to try this, I'd love to see it here. Or if I somehow happen upon it, I will put it up... Thank you!!

 

[Aladin Akyurek]

Good morning, thank you Misca, Aladin!

These didn't work for me, so I am sure I am doing something wrong.

In the formulas you sent me, I get 155 if using DG2, and 156 if using DG1.

The row where 1211 is residing up top on this worksheet is 17 in case that's useful.

In case there's another way to try this, I'd love to see it here. Or if I somehow happen upon it, I will put it up... Thank you!!

What do we get if we try:

=ISNUMBER(DG17)

somewhere else?

 

[excelsishya]

Hi ,

May be i think you are looking for absolute row number not relative row number

You can use Vlookup after serial numbering column next to lookup array data.


<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Calibri,Arial; font-size: 11pt;" border="0" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 84px;"> <col style="width: 162px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>F</td> <td>G</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td>
</td> <td style="text-align: right;">1</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>
</td> <td style="text-align: right;">2</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>
</td> <td style="text-align: right;">3</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>
</td> <td style="text-align: right;">4</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="text-align: right;">2</td> <td style="text-align: right;">5</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="text-align: right;">21</td> <td style="text-align: right;">6</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td style="text-align: right;">1</td> <td style="text-align: right;">7</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td>lookup array</td> <td>serially numbered</td></tr></tbody></table>


<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Calibri,Arial; font-size: 11pt;" border="0" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>B</td> <td>C</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>Looup</td> <td>
</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="text-align: right;">1</td> <td style="text-align: right;">7</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="text-align: right;">21</td> <td style="text-align: right;">6</td></tr> <tr style="height: 18px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="text-align: right;">2</td> <td style="text-align: right;">5</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); font-family: Arial; color: rgb(0, 0, 0); font-size: 10pt;"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>C3</td> <td>=VLOOKUP(B3,$F$1:$G$7,2,0)</td></tr> <tr> <td>C4</td> <td>=VLOOKUP(B4,$F$1:$G$7,2,0)</td></tr> <tr> <td>C5</td> <td>=VLOOKUP(B5,$F$1:$G$7,2,0)</td></tr></tbody></table></td></tr></tbody></table>