VLOOKUP Question!

[locky1099]

Hi there,

I am using Microsoft Excel 2007.

I have a slight problem autofilling this formula across my sheet.

This is the situation...

I am creating a workbook for Football (Soccer) Referees.

The first page shows all the fixture information (Game number, Date, Time, Venue, Home Team, Away Team etc.)

The second page is for referees to record any cautions they issue.

Okay, so I have assigned a unique game number to each fixture on page one (001, 002, 003 etc.).

The columns on the second page are as follows:

(Game Number, Date, Home Team, Away Team, Player's Name, Team, Offence).

I have used a VLOOKUP function to automatically fill in 'Date', 'Home Team' and 'Away Team', in the first row of each column.

It reads as follows: =VLOOKUP(A4, 'Fixture + Results Sheet'!A6:Q65,2, 0) for the 'Date' Column.

A4 being the lookup value.
A6:Q65 being the table array
2 being the 'second column' in the table (in this case 'Date')
0 meaning exact match only

When I try to drag this formula down the column, it changes to:
=VLOOKUP(A5, 'Fixture + Results Sheet'!A7:Q66, 2, 0)

I need the lookup value to be A5, so that is correct.

However, the table array is still the same (A6:Q65).

How do I get it autofill this, as I don't wish to write out this formula hundreds of times?

Thanks,

Jordan

 

[Zyel]

Hi there,

I am using Microsoft Excel 2007.

I have a slight problem autofilling this formula across my sheet.

This is the situation...

I am creating a workbook for Football (Soccer) Referees.

The first page shows all the fixture information (Game number, Date, Time, Venue, Home Team, Away Team etc.)

The second page is for referees to record any cautions they issue.

Okay, so I have assigned a unique game number to each fixture on page one (001, 002, 003 etc.).

The columns on the second page are as follows:

(Game Number, Date, Home Team, Away Team, Player's Name, Team, Offence).

I have used a VLOOKUP function to automatically fill in 'Date', 'Home Team' and 'Away Team', in the first row of each column.

It reads as follows: =VLOOKUP(A4, 'Fixture + Results Sheet'!A6:Q65,2, 0) for the 'Date' Column.

A4 being the lookup value.
A6:Q65 being the table array
2 being the 'second column' in the table (in this case 'Date')
0 meaning exact match only

When I try to drag this formula down the column, it changes to:
=VLOOKUP(A5, 'Fixture + Results Sheet'!A7:Q66, 2, 0)

I need the lookup value to be A5, so that is correct.

However, the table array is still the same (A6:Q65).

How do I get it autofill this, as I don't wish to write out this formula hundreds of times?

Thanks,

Jordan

use $ on your vlookup

=VLOOKUP(A4, 'Fixture + Results Sheet'!$A$6:$Q$65,2,False)

btw, i use False and not 0 for the exact match criteria

 

[locky1099]

Thank you so much!

 

[locky1099]

Is there any way I can get it to stop showing #N/A when the Lookup Value column is blank?

It's because this spreadsheet is not for my personal use, and is progressively filled in over the season, and it gets annoying to look at already!

Thanks,

Jordan

 

[Zyel]

Is there any way I can get it to stop showing #N/A when the Lookup Value column is blank?

It's because this spreadsheet is not for my personal use, and is progressively filled in over the season, and it gets annoying to look at already!

Thanks,

Jordan

I'm assuming ur vlookup formula is at Column B.

So at Column C
=IF(ISNA(B2)=True,"",B2)

 

[locky1099]

Well the current formula reads:

=VLOOKUP(B4, 'Fixture + Results Sheet'!$A$6:$Q$65, 2, 0)

That displays #N/A

How should it now be written to get rid of the error message.

Sorry - I'm not an expert like all you guys.

Lol

Thanks,

Jordan

 

[locky1099]

I'm assuming ur vlookup formula is at Column B.

So at Column C
=IF(ISNA(B2)=True,"",B2)

<HR style="BACKGROUND-COLOR: #ffffff; COLOR: #ffffff" SIZE=1> <!-- / icon and title --><!-- message -->
Well the current formula reads:

=VLOOKUP(B4, 'Fixture + Results Sheet'!$A$6:$Q$65, 2, 0)

That displays #N/A

How should it now be written to get rid of the error message.

Sorry - I'm not an expert like all you guys.

Lol

Thanks,

Jordan

 

[locky1099]

Anyone?

 

[repairman615]

Hello,
how about:

=iferror(VLOOKUP(B4, 'Fixture + Results Sheet'!$A$6:$Q$65, 2, 0),"")

 

[locky1099]

Hello,
how about:

=iferror(VLOOKUP(B4, 'Fixture + Results Sheet'!$A$6:$Q$65, 2, 0),"")

Worked perfectly.

Thanks.