ExcelBanter - #N/A result because data is on another worksheet

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - #N/A result because data is on another worksheet (https://www.excelbanter.com/excel-worksheet-functions/254073-n-result-because-data-another-worksheet.html)

Steve

#N/A result because data is on another worksheet

I have this formula which refers to the Master Tab.

=VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)

It usually works great, however, when the data is not found ( because it's
on another worksheet ( Minor Tab!), I get a #N/A.

How can I re-write the formula that if the #N/A is produced, it does the
Vlookup on the Minor tab! instead of the master tab!

Something like: If =VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE) produces a
#N/A, VLOOKUP($B4,MinorTab!$H$21:$O$923,8,FALSE, if not, use this original
formula =VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)

Thanks,

Steve

Glenn

#N/A result because data is on another worksheet

Steve wrote:
I have this formula which refers to the Master Tab.

=VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)

It usually works great, however, when the data is not found ( because it's
on another worksheet ( Minor Tab!), I get a #N/A.

How can I re-write the formula that if the #N/A is produced, it does the
Vlookup on the Minor tab! instead of the master tab!

Something like: If =VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE) produces a
#N/A, VLOOKUP($B4,MinorTab!$H$21:$O$923,8,FALSE, if not, use this original
formula =VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)

Thanks,

Steve

=IF(ISNA(VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALS E)),
VLOOKUP($B4,MinorTab!$H$21:$O$923,8,FALSE),
VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE))

T. Valko

#N/A result because data is on another worksheet

As long as the lookup value is on one or the other sheets...

=VLOOKUP($B4,IF(COUNTIF(MasterTab!$H$21:$H$923,$B4 ),MasterTab!$H$21:$O$923,MinorTab!$H$21:$O$923),8, 0)

--
Biff
Microsoft Excel MVP

"Steve" wrote in message
...
I have this formula which refers to the Master Tab.

=VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)

It usually works great, however, when the data is not found ( because it's
on another worksheet ( Minor Tab!), I get a #N/A.

How can I re-write the formula that if the #N/A is produced, it does the
Vlookup on the Minor tab! instead of the master tab!

Something like: If =VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE) produces
a
#N/A, VLOOKUP($B4,MinorTab!$H$21:$O$923,8,FALSE, if not, use this original
formula =VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)

Thanks,

Steve

Steve

#N/A result because data is on another worksheet

Thank you very much. This worked great.

Steve

"Glenn" wrote:

Steve wrote:
I have this formula which refers to the Master Tab.

=VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)

It usually works great, however, when the data is not found ( because it's
on another worksheet ( Minor Tab!), I get a #N/A.

How can I re-write the formula that if the #N/A is produced, it does the
Vlookup on the Minor tab! instead of the master tab!

Something like: If =VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE) produces a
#N/A, VLOOKUP($B4,MinorTab!$H$21:$O$923,8,FALSE, if not, use this original
formula =VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)

Thanks,

Steve

=IF(ISNA(VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALS E)),
VLOOKUP($B4,MinorTab!$H$21:$O$923,8,FALSE),
VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE))
.

Steve

#N/A result because data is on another worksheet

The lookup value would be on either the master sheet or the minor sheet, but
not both. There also may be a situation where it would not be on either.
Trying the formula, results in a #REF!

For the particular lookup value I'm using, that value in on the master sheet.

"T. Valko" wrote:

As long as the lookup value is on one or the other sheets...

=VLOOKUP($B4,IF(COUNTIF(MasterTab!$H$21:$H$923,$B4 ),MasterTab!$H$21:$O$923,MinorTab!$H$21:$O$923),8, 0)

--
Biff
Microsoft Excel MVP

"Steve" wrote in message
...
I have this formula which refers to the Master Tab.

=VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)

It usually works great, however, when the data is not found ( because it's
on another worksheet ( Minor Tab!), I get a #N/A.

How can I re-write the formula that if the #N/A is produced, it does the
Vlookup on the Minor tab! instead of the master tab!

Something like: If =VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE) produces
a
#N/A, VLOOKUP($B4,MinorTab!$H$21:$O$923,8,FALSE, if not, use this original
formula =VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)

Thanks,

Steve

.

T. Valko

#N/A result because data is on another worksheet

Trying the formula, results in a #REF!

Hmmm...

Works just fine for me. However:

There also may be a situation where it would not be on either.

In that case, the formula would return #N/A. So, what result do you want
when the lookup value isn't on either sheet?

--
Biff
Microsoft Excel MVP

"Steve" wrote in message
...
The lookup value would be on either the master sheet or the minor sheet,
but
not both. There also may be a situation where it would not be on either.
Trying the formula, results in a #REF!

For the particular lookup value I'm using, that value in on the master
sheet.

"T. Valko" wrote:

As long as the lookup value is on one or the other sheets...

=VLOOKUP($B4,IF(COUNTIF(MasterTab!$H$21:$H$923,$B4 ),MasterTab!$H$21:$O$923,MinorTab!$H$21:$O$923),8, 0)

--
Biff
Microsoft Excel MVP

"Steve" wrote in message
...
I have this formula which refers to the Master Tab.

=VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)

It usually works great, however, when the data is not found ( because
it's
on another worksheet ( Minor Tab!), I get a #N/A.

How can I re-write the formula that if the #N/A is produced, it does
the
Vlookup on the Minor tab! instead of the master tab!

Something like: If =VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)
produces
a
#N/A, VLOOKUP($B4,MinorTab!$H$21:$O$923,8,FALSE, if not, use this
original
formula =VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)

Thanks,

Steve

.

Steve

#N/A result because data is on another worksheet

Ok, It works now. You inadvertently had H - H on the first Master tab
reference instead of H - O, and I also eneterd it with both H's. Changing
the 2nd H to an O makes it work great.

Thanks,

Steve

"T. Valko" wrote:

As long as the lookup value is on one or the other sheets...

=VLOOKUP($B4,IF(COUNTIF(MasterTab!$H$21:$H$923,$B4 ),MasterTab!$H$21:$O$923,MinorTab!$H$21:$O$923),8, 0)

--
Biff
Microsoft Excel MVP

"Steve" wrote in message
...
I have this formula which refers to the Master Tab.

=VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)

It usually works great, however, when the data is not found ( because it's
on another worksheet ( Minor Tab!), I get a #N/A.

How can I re-write the formula that if the #N/A is produced, it does the
Vlookup on the Minor tab! instead of the master tab!

Something like: If =VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE) produces
a
#N/A, VLOOKUP($B4,MinorTab!$H$21:$O$923,8,FALSE, if not, use this original
formula =VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)

Thanks,

Steve

.

Steve

#N/A result because data is on another worksheet

#N/A will be ok.

Thanks,

"T. Valko" wrote:

Trying the formula, results in a #REF!

Hmmm...

Works just fine for me. However:

There also may be a situation where it would not be on either.

In that case, the formula would return #N/A. So, what result do you want
when the lookup value isn't on either sheet?

--
Biff
Microsoft Excel MVP

"Steve" wrote in message
...
The lookup value would be on either the master sheet or the minor sheet,
but
not both. There also may be a situation where it would not be on either.
Trying the formula, results in a #REF!

For the particular lookup value I'm using, that value in on the master
sheet.

"T. Valko" wrote:

As long as the lookup value is on one or the other sheets...

=VLOOKUP($B4,IF(COUNTIF(MasterTab!$H$21:$H$923,$B4 ),MasterTab!$H$21:$O$923,MinorTab!$H$21:$O$923),8, 0)

--
Biff
Microsoft Excel MVP

"Steve" wrote in message
...
I have this formula which refers to the Master Tab.

=VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)

It usually works great, however, when the data is not found ( because
it's
on another worksheet ( Minor Tab!), I get a #N/A.

How can I re-write the formula that if the #N/A is produced, it does
the
Vlookup on the Minor tab! instead of the master tab!

Something like: If =VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)
produces
a
#N/A, VLOOKUP($B4,MinorTab!$H$21:$O$923,8,FALSE, if not, use this
original
formula =VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)

Thanks,

Steve

.

.

T. Valko

#N/A result because data is on another worksheet

OK, thanks for the feedback!

--
Biff
Microsoft Excel MVP

"Steve" wrote in message
...
#N/A will be ok.

Thanks,

"T. Valko" wrote:

Trying the formula, results in a #REF!

Hmmm...

Works just fine for me. However:

There also may be a situation where it would not be on either.

In that case, the formula would return #N/A. So, what result do you want
when the lookup value isn't on either sheet?

--
Biff
Microsoft Excel MVP

"Steve" wrote in message
...
The lookup value would be on either the master sheet or the minor
sheet,
but
not both. There also may be a situation where it would not be on
either.
Trying the formula, results in a #REF!

For the particular lookup value I'm using, that value in on the master
sheet.

"T. Valko" wrote:

As long as the lookup value is on one or the other sheets...

=VLOOKUP($B4,IF(COUNTIF(MasterTab!$H$21:$H$923,$B4 ),MasterTab!$H$21:$O$923,MinorTab!$H$21:$O$923),8, 0)

--
Biff
Microsoft Excel MVP

"Steve" wrote in message
...
I have this formula which refers to the Master Tab.

=VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)

It usually works great, however, when the data is not found (
because
it's
on another worksheet ( Minor Tab!), I get a #N/A.

How can I re-write the formula that if the #N/A is produced, it does
the
Vlookup on the Minor tab! instead of the master tab!

Something like: If =VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)
produces
a
#N/A, VLOOKUP($B4,MinorTab!$H$21:$O$923,8,FALSE, if not, use this
original
formula =VLOOKUP($B4,MasterTab!$H$21:$O$923,8,FALSE)

Thanks,

Steve

.

.

All times are GMT +1. The time now is 07:47 AM.