vlookup problem

[ditchy]

Hi there excellers
I have a problem with vlookup returning #N/A
sheet 1
A B C
1 race# name points
2
3

sheet 2
A B C D E F
1 points date name age sex race#
2
3
formula is
entered in cell c2(sheet1) =if(a2>=0,vlookup(a2,sheet2$a$1:$f$100,1,FALSE),"")

I need the points from sheet2(a2:a100)to be put in sheet1(c2:c100)
is this the correct formula? can someone help
thanks
Ditchy, Ballarat Australia

 

[Don Guillett]

after sheet2 you need a !

 

[RagDyer]

Try this in C2 and copy down as needed:
=IF(ISNA(MATCH(A2,Sheet2!$F$2:$F$100,0)),"",INDEX(Sheet2!$A$2:$A$100,MATCH(A
2,Sheet2!$F$2:$F$100,0)))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi there excellers
I have a problem with vlookup returning #N/A
sheet 1
A B C
1 race# name points
2
3

sheet 2
A B C D E F
1 points date name age sex race#
2
3
formula is
entered in cell c2(sheet1)
=if(a2>=0,vlookup(a2,sheet2$a$1:$f$100,1,FALSE),"")

I need the points from sheet2(a2:a100)to be put in sheet1(c2:c100)
is this the correct formula? can someone help
thanks
Ditchy, Ballarat Australia

 

[JE McGimpsey]

Since you're looking up a value on the right side of your table, use the
more general INDEX(MATCH(...)). One way:

C2: =IF(A2="","",INDEX(Sheet2!A:A,MATCH(A2,Sheet2!F:F,FALSE)))

or, if the table may not have the race in it:

C2: =IF(A2="","",IF(ISNA(MATCH(A2,Sheet2!F:F,FALSE)),"Not
found",INDEX(Sheet2!A:A,MATCH(A2,Sheet2!F:F,FALSE))))

 

[ditchy]

Since you're looking up a value on the right side of your table, use the
more general INDEX(MATCH(...)). One way:

C2: =IF(A2="","",INDEX(Sheet2!A:A,MATCH(A2,Sheet2!F:F,FALSE)))

or, if the table may not have the race in it:

C2: =IF(A2="","",IF(ISNA(MATCH(A2,Sheet2!F:F,FALSE)),"Not
found",INDEX(Sheet2!A:A,MATCH(A2,Sheet2!F:F,FALSE))))

Thank you all for your help, you have solved my problem. I have used the method suggested by JE McGimpsey. I will try the other formulas to see which suits me best.

Thank you again
Ditchy