Am I even using the right function?

[Guest]

Hi

I am trying to figure out if I can use VLOOKUP to check a range of cells. I
am probably using the wrong fuction and there is much simpler way of doing
this -
I have 3 sheets in I3 on sheet 1 I want to put in a formula that checks if
the values on sheet 2 G2 to J2 to match any of the values on sheet 3 A13 to
A20 (i've called the table array masters!) and if they do I want them to put
this same value into I3. I hope that makes sense! Can anyone please tell me
if this is at all possible and if so how I do it?

Thank you very much

 

[Pete_UK]

What happens if more than one of the values in G2:J2 match with the
values in A13:A20 of sheet 3 ?

Look at using the MATCH function in Excel Help - you will probably
have to use this in conjunction with ISNA to trap errors which will
occur if there is no match.

Hope this helps.

Pete

 

[Guest]

Actually, I think you want HLOOKUP.

=HLOOKUP(I3,'Sheet2!G2:J2,1,FALSE)

HTH,
Barb Reinhardt

 

[Guest]

Thank you for your responses!!! However this doesn't seem to work! Maybe
because there's no mention of sheet 3!! Am I trying to do too much maybe?

 

[Guest]

Thank you for your replies! There definately won't be more than one value
matching A13:A20!
I've tried matching but I don't seem to be able to get this to put the same
in text it I3 that matches! If that makes sense??!! Maybe it is too difficult
a formula?

 

[Pete_UK]

MATCH will help you to find if there is a match, but you need to use
INDEX to return the value that matches. I've tested this formula out
on some sample data and it works, though it is a bit of a nightmare:

=IF(MAX(IF(ISNA(MATCH(Sheet2!G$2,Sheet3!$A$13:$A$20,0)),0,MATCH(Sheet2!
G$2,Sheet3!$A$13:$A$20,0)),IF(ISNA(MATCH(Sheet2!H$2,Sheet3!$A$13:$A
$20,0)),0,MATCH(Sheet2!H$2,Sheet3!$A$13:$A$20,0)),IF(ISNA(MATCH(Sheet2!
I$2,Sheet3!$A$13:$A$20,0)),0,MATCH(Sheet2!I$2,Sheet3!$A$13:$A
$20,0)),IF(ISNA(MATCH(Sheet2!J$2,Sheet3!$A$13:$A$20,0)),0,MATCH(Sheet2!
J$2,Sheet3!$A$13:$A$20,0)))=0,0,INDEX(Sheet3!$A$13:$A
$20,MAX(IF(ISNA(MATCH(Sheet2!G$2,Sheet3!$A$13:$A$20,0)),0,MATCH(Sheet2!
G$2,Sheet3!$A$13:$A$20,0)),IF(ISNA(MATCH(Sheet2!H$2,Sheet3!$A$13:$A
$20,0)),0,MATCH(Sheet2!H$2,Sheet3!$A$13:$A$20,0)),IF(ISNA(MATCH(Sheet2!
I$2,Sheet3!$A$13:$A$20,0)),0,MATCH(Sheet2!I$2,Sheet3!$A$13:$A
$20,0)),IF(ISNA(MATCH(Sheet2!J$2,Sheet3!$A$13:$A$20,0)),0,MATCH(Sheet2!
J$2,Sheet3!$A$13:$A$20,0)))))

Perhaps an array function would have been shorter. This is all one
formula - watch out for line-breaks.

This returns 0 if there is no match, otherwise it returns the value
from A13:A20 of Sheet3 which matches any of the entries is G2:J2 of
Sheet2. If you are not too bothered about an error being returned in
cell I3, then this shorter version will return #VALUE if there is no
match:

=INDEX(Sheet3!$A$13:$A$20,MAX(IF(ISNA(MATCH(Sheet2!G$2,Sheet3!$A$13:$A
$20,0)),0,MATCH(Sheet2!G$2,Sheet3!$A$13:$A$20,0)),IF(ISNA(MATCH(Sheet2!
H$2,Sheet3!$A$13:$A$20,0)),0,MATCH(Sheet2!H$2,Sheet3!$A$13:$A
$20,0)),IF(ISNA(MATCH(Sheet2!I$2,Sheet3!$A$13:$A$20,0)),0,MATCH(Sheet2!
I$2,Sheet3!$A$13:$A$20,0)),IF(ISNA(MATCH(Sheet2!J$2,Sheet3!$A$13:$A
$20,0)),0,MATCH(Sheet2!J$2,Sheet3!$A$13:$A$20,0))))

You could always trap the error in another cell using IF(ISERROR(I3)
etc...

Hope this helps.

Pete

 

[Guest]

Give us one specific problem to solve. The 3 sheets in I3 lost me.