can lookup a number to a 10x10 table?

[foxxkat]

hi,
how can i lookup a number to 10rows by 10columns (total 100 cells) of data?

Cell A1
1234

Cell B1 to Cell K10 are filled with random 4-digit numbers, and L1-L10 are text to be displayed when a match is found.

want to see if the number at A1 matches any in the B1-K10 table, then display the text on Column L.
the B1-K10 cannot be sorted bcoz they are random and changes overtime.

was experimenting with vlookup & hlookup but couldn't make it.

iF it has a match, let's say formula on cell A2 then A2 will just display "Match" or "Invalid" etc based on the text input in L1 to L10

suggestions welcome.
thanks,

[Alex Delamain]

you will need to use match()

for the random table use =RANDBETWEEN(1000,9999)

If column L contains the text to display then try column M as

=IF(ISNA(MATCH($A$1,B1:K1,0))=TRUE,"",+L1)

so if there is a match in that row it will pick up the text in col L

[mangesh_yadav]

Use the following formula:

=INDEX(L1:L10,SUMPRODUCT(--($A$1=B1:K10),(IF(B1:K10<>$A$1,0,ROW(B1:K10)))))

confirm with control + shift + enter as this is an array formula

- Mangesh

[olasa]

...

A1: 1234 (find this value)
B1:K10 : 10X10 matrix (in this matrix)
L1:L10 : Lookup value (and then display this text)

=CHOOSE(MATCH(COUNTIF(B1:K10,A1),{0,1,2}),"Nothing",INDEX(L1:L10,SUMPRODUCT((B1:K10=A1)*ROW(L1:L10))),"More then one value")

Ola Sandström