Look up unique value over several columns of data

[Guest]

Hi,

Can anyone think of a way to lookup a value in an table that covers several
columns of unique discreet values and then returns the row number in which
that value occurs regardless of the column number?

E.g. B24 contains unique discreet values and one of these value is 3,
which is in cell C4. The lookup formula searches for 3 in columns B and
returns 4 for the row number.

MATCH seems to only handle one column at a time. I realize that I could
write this functionality with a nested IF statement, but imagine that the
table covers many columns, in which case the IF statement method would be
impractical.

Hope this makes sense.

 

[Domenic]

Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=MIN(IF($B$2:$D$4=3,ROW($B$2:$D$4)))

If you'd like to return NA (Not Available) when the number doesn't
exist, try the following formula instead...

=IF(COUNTIF($B$2:$D$4,3),MIN(IF($B$2:$D$4=3,ROW($B$2:$D$4))),"NA")

Hope this helps!

 

[Guest]

Hi,

If I understand your question:

=MAX((B24=B8)*ROW(B24))

Where the number you want to find is in cell B8.

This is an array formula so press Shift Ctrl Enter to enter in not Enter.

Since they are unique numbers in the range, you could use MIN, SUM, COUNT,
AVERAGe instead of MAX. You could also use SUMPRODUCT and then you wouldn't
neet to do an array entry.

=SUMPRODUCT((B24=B8)*ROW(B24))