Vlookup in 4 columns

  • Thread starter Thread starter deeds
  • Start date Start date
D

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
 
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: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.
 
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!
 
Another one...

Data to be reurned in the range E1:E10
Numbers in the range A1:D10

Lookup number in H1

Array entered** :

=INDEX(E1:E10,MATCH(1,--(MMULT(--(A1:D10=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)
 
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
 
Great Work folks! Got it to work with all examples....now I just choose one
and go with it! Thanks again!
 
Back
Top