vlookup on 2 columns

Y

yaniv.dg

hi trying to vlookup on 2 culomns but im sure its not the way
i need to compare 1 culomn find all the data there and with this to
compare another data in another culomn based on this 2 to bring data
from third culomn
i cannot find a solution for this
 
B

Bob Phillips

=INDEX(third_column,MATCH(1,(column1=value1)*(column2=value2),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

although I say column1 etc., but you cannot use a whole column, only part of
it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Y

yaniv.dg

hi mark
do you this there is a way to use the current functions without vba
becaouse the execute is coming from access

my vlookup needs to be as folow

for example:
need to find 36 where the leter e is chosen that means:


36 a
36 b
36 c
36 e

so the data willcome only from the row on 36 e but the question how can
we monitor both of the culomns in order to find the correct row to use


Mark Ivey כתב:
 
Y

yaniv.dg

hi,
im almost close to a solution,
my problem is the i my formula will be execute from access,
how should i will implement enter+ctrl+shift when i'm doing automation?
 
Y

yaniv.dg

thank you very much for your help,it was very helpfull inforamtion that
i used

now it became little bit more complex becaouse i need to find data in
once culomn and based on it to compare a range
for example:
36-d a
36 d-f
36 k-r
36 s-t

so i need for example to find the culomn where "e" is sitting

Dave Peterson כתב:
 
D

Dave Peterson

This formula isn't an array formula and doesn't require you use
ctrl-shift-enter.
 
D

Dave Peterson

I don't understand the question or the example and I've never used Access.

Maybe someone else will jump in.
 
B

Bob Phillips

So you tried

=INDEX(C2:C200,MATCH(1,(H19=A2:A200)*(G19>=LEFT(B2:B200,1))*(G19<=RIGHT(B2:B
200,1)),0))

did you, and you array entered it?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

yes i know,i tried this with ranging the culomns,it still doesnt work
 
Y

yaniv.dg

hi bob,
i think i need to divide the data to 2 culomns and thats it becaouse it
seems like when i'm trying to use left and right in an array it doesnt
not work

Bob Phillips כתב:
So you tried

=INDEX(C2:C200,MATCH(1,(H19=A2:A200)*(G19>=LEFT(B2:B200,1))*(G19<=RIGHT(B2:B
200,1)),0))

did you, and you array entered it?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

yes i know,i tried this with ranging the culomns,it still doesnt work
Click to expand...
 
Y

yaniv.dg

hi bob,
i have some problem in the formula that i showed you,from some reason
H19(see below) is compared to TRUE and FALSE and H19,G19 compared to
1,so when i'm matching between the two i have all the time error in
vba,
in the excel formula online its ok,i'm comparing all for them to 1 but
in the VBA code it cannot be matched together,
do you have a solution for me?
 
Top