Vlookup in 4 columns

[deeds]

I need to check 4 columns of data for a number....when it finds it in ANY
column it returns the text in column 5. The number will appear in only 1
column but could show up in ANY of the 4 columns. Any ideas?

Thanks in advance

 

[Dave]

Hi,
There has to be a better way than the one I am about to show, but it seems
to work, so it'll do for starters.
Lookup number in G1
You'll need 4 helper cells. I've used G2, H2, I2, J2
G2 =MATCH($G$1,A:A,0)
H2 =MATCH($G$1,B:B,0)
I2 =MATCH($G$1,C:C,0)
J2 =MATCH($G$1,D,0)

G2 =INDIRECT("E"&SUMIF(G2:H2:I2:J2,">0"))
G2 returns the text you want.
I tried to include the 4 Match formulas inside a single SUMIF, but couldn't
get it to work. Perhaps one of the pro's will enlighten us.
Regards - Dave.

 

[Dave]

Actually, G2 only needs to be:
=INDIRECT("E"&SUMIF(G2:J2,">0"))
Dave.

 

[Domenic]

Assumptions:

A2:A10 contains the text to return

B2:E10 contains the data in which to search

G2 contains the number of interest

Formula:

=INDEX($A$2:$A$10,SMALL(IF($B$2:$E$10=G2,ROW($B$2:$E$10)-ROW($B$2)+1),1))

....confirmed with CONTROL+SHIFT+ENTER. Adjust the references/ranges
accordingly. Note that if there's more than one row that contains the
number of interest, the formula will return the first occurrence.

Hope this helps!

 

[T. Valko]

Another one...

Data to be reurned in the range E1:E10
Numbers in the range A110

Lookup number in H1

Array entered** :

=INDEX(E1:E10,MATCH(1,--(MMULT(--(A110=H1),{1;1;1;1})>0),0))

This is limited to ~5460 rows of data.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Max]

I posted the response below in your earlier thread
-----------------
As for your new query, my thoughts would be to try something along these
lines, indicatively:

=
IF(ISNA(MATCH(1)),IF(MATCH(2)),
IF(ISNA(MATCH(3)),IF(ISNA(MATCH(4)),
INDEX(ColE,MATCH(4)),INDEX(ColE,MATCH(3)),
INDEX(ColE,MATCH(2)),INDEX(ColE,MATCH(1)))

where 1,2,3,4 would contain the sequential checks on the 4 cols

 

[deeds]

Great Work folks! Got it to work with all examples....now I just choose one
and go with it! Thanks again!