If functions Looking at ranges

[Guest]

All,

I hope someone has an answer for this.

I am trying to get a formula based on the If function, and within this is a
vlookup function. i.e nested. The formula i have presently is coming with the
result false,
the formula is as follows
IF(D41='[Actual 2007.xls]Sheet1'!$A$2:$A$3690,IF(C41='[Actual
2007.xls]Sheet1'!$B$2:$B$3690,VLOOKUP(C41,'[Actual
2007.xls]Sheet1'!$B$2:$F$3691,2,FALSE))

Question, the IF function can it give a result if it is pointed at a range.

Thanks

Tunde

 

[Guest]

No ... the IF function is wrong: you need to use MATCH.

What exactly are you trying to do:

If D41 is found in column A and C41 is found in Column B, then lookup up the
value in Column C based on C41 as the search?

Try this entered with Ctrl+Shift+Enter

=INDEX('[Actual 2007.xls]Sheet1'!$C$2:$C$369,MATCH(1,(D41='[Actual
2007.xls]eet1'!$A$2:$A$3690)*(C41='[Actual 2007.xls]Sheet1'!$B$2:$B$3690),0))

It assumes D41 & C41 are found and will return (row) value in column C where
C41 & D41 coincide.

HTH

 

[Guest]

Topper,

Thanks for your response.
You have correctly assesed what I was tyring to do, but the formula you have
given should have worked, but it has not. It has given a result of #N/A.
Any ideas?

Tunde

No ... the IF function is wrong: you need to use MATCH.

What exactly are you trying to do:

If D41 is found in column A and C41 is found in Column B, then lookup up the
value in Column C based on C41 as the search?

Try this entered with Ctrl+Shift+Enter

=INDEX('[Actual 2007.xls]Sheet1'!$C$2:$C$369,MATCH(1,(D41='[Actual
2007.xls]eet1'!$A$2:$A$3690)*(C41='[Actual 2007.xls]Sheet1'!$B$2:$B$3690),0))

It assumes D41 & C41 are found and will return (row) value in column C where
C41 & D41 coincide.

HTH

All,

I hope someone has an answer for this.

I am trying to get a formula based on the If function, and within this is a
vlookup function. i.e nested. The formula i have presently is coming with the
result false,
the formula is as follows
IF(D41='[Actual 2007.xls]Sheet1'!$A$2:$A$3690,IF(C41='[Actual
2007.xls]Sheet1'!$B$2:$B$3690,VLOOKUP(C41,'[Actual
2007.xls]Sheet1'!$B$2:$F$3691,2,FALSE))

Question, the IF function can it give a result if it is pointed at a range.

Thanks

Tunde

 

[Guest]

You will get #N/A if the match fails.

=IF(ISNA(INDEX('[Actual 2007.xls]Sheet1'!$C$2:$C$369,MATCH(1,(D41='[Actual
2007.xls]eet1'!$A$2:$A$3690)*(C41='[Actual
2007.xls]Sheet1'!$B$2:$B$3690),0))),"",INDEX('[Actual
2007.xls]Sheet1'!$C$2:$C$369,MATCH(1,(D41='[Actual
2007.xls]eet1'!$A$2:$A$3690)*(C41='[Actual 2007.xls]Sheet1'!$B$2:$B$3690),0)))

will return blank if #N/A occurs.

Are you sure the C41 & D41 match is TRUE in your test?

Topper,

Thanks for your response.
You have correctly assesed what I was tyring to do, but the formula you have
given should have worked, but it has not. It has given a result of #N/A.
Any ideas?

Tunde

No ... the IF function is wrong: you need to use MATCH.

What exactly are you trying to do:

If D41 is found in column A and C41 is found in Column B, then lookup up the
value in Column C based on C41 as the search?

Try this entered with Ctrl+Shift+Enter

=INDEX('[Actual 2007.xls]Sheet1'!$C$2:$C$369,MATCH(1,(D41='[Actual
2007.xls]eet1'!$A$2:$A$3690)*(C41='[Actual 2007.xls]Sheet1'!$B$2:$B$3690),0))

It assumes D41 & C41 are found and will return (row) value in column C where
C41 & D41 coincide.

HTH

All,

I hope someone has an answer for this.

I am trying to get a formula based on the If function, and within this is a
vlookup function. i.e nested. The formula i have presently is coming with the
result false,
the formula is as follows
IF(D41='[Actual 2007.xls]Sheet1'!$A$2:$A$3690,IF(C41='[Actual
2007.xls]Sheet1'!$B$2:$B$3690,VLOOKUP(C41,'[Actual
2007.xls]Sheet1'!$B$2:$F$3691,2,FALSE))

Question, the IF function can it give a result if it is pointed at a range.

Thanks

Tunde