Vlookup across multiple tabs

Is there a formula to incorporate a vlookup scanning multiple tabs.

i.e.
Total Tab will scan "GMP" "GMT" "FMP" & "TRN"

returning #n/a for the missing information and the first line item found on
the rest.

Thanks in advance.

unfortunaltely, the best metod I know of is
=if(iserror(Vlookup(ref,GMP!range,2,0)),if(iserror(Vlookup(ref,FMP!range,2,0)),if(iserror(...)
...,Vlookup(ref,FMP!range,2,0),Vlookup(ref,GMP!range,2,0))))


"Hirsch" wrote:

> Is there a formula to incorporate a vlookup scanning multiple tabs.
>
> i.e.
> Total Tab will scan "GMP" "GMT" "FMP" & "TRN"
>
> returning #n/a for the missing information and the first line item found on
> the rest.
>
> Thanks in advance.

unfortunaltely, the best metod I know of is
=if(iserror(Vlookup(ref,GMP!range,2,0)),if(iserror(Vlookup(ref,FMP!range,2,0)),if(iserror(...)
...,Vlookup(ref,FMP!range,2,0),Vlookup(ref,GMP!range,2,0))))


"Hirsch" wrote:

> Is there a formula to incorporate a vlookup scanning multiple tabs.
>
> i.e.
> Total Tab will scan "GMP" "GMT" "FMP" & "TRN"
>
> returning #n/a for the missing information and the first line item found on
> the rest.
>
> Thanks in advance.

unfortunaltely, the best metod I know of is
=if(iserror(Vlookup(ref,GMP!range,2,0)),if(iserror(Vlookup(ref,FMP!range,2,0)),if(iserror(...)
...,Vlookup(ref,FMP!range,2,0),Vlookup(ref,GMP!range,2,0))))


"Hirsch" wrote:

> Is there a formula to incorporate a vlookup scanning multiple tabs.
>
> i.e.
> Total Tab will scan "GMP" "GMT" "FMP" & "TRN"
>
> returning #n/a for the missing information and the first line item found on
> the rest.
>
> Thanks in advance.

unfortunaltely, the best metod I know of is
=if(iserror(Vlookup(ref,GMP!range,2,0)),if(iserror(Vlookup(ref,FMP!range,2,0)),if(iserror(...)
...,Vlookup(ref,FMP!range,2,0),Vlookup(ref,GMP!range,2,0))))


"Hirsch" wrote:

> Is there a formula to incorporate a vlookup scanning multiple tabs.
>
> i.e.
> Total Tab will scan "GMP" "GMT" "FMP" & "TRN"
>
> returning #n/a for the missing information and the first line item found on
> the rest.
>
> Thanks in advance.

Hi!

Make a list of the sheet names:

J1 = GMP
J2 = GMT
J3 = FMP
J4 = TRN

Name that list something like SList.

This will work as long as the table array is the same on each sheet.

Enetered as an array using the key combo of CTRL,SHIFT,ENTER:

=VLOOKUP(A1,INDIRECT("'"&INDEX(SList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SList&"'!A1:A10"),A1)>0,0))&"'!A1:C10"),2,0)

A1 is the lookup value

This is how that formula would look as it operates on a single sheet:

=VLOOKUP(A1,GMP!A1:C10,2,0)

Biff

"Hirsch" <[email protected]> wrote in message
news:[email protected]...
> Is there a formula to incorporate a vlookup scanning multiple tabs.
>
> i.e.
> Total Tab will scan "GMP" "GMT" "FMP" & "TRN"
>
> returning #n/a for the missing information and the first line item found
> on
> the rest.
>
> Thanks in advance.

Hi!

Make a list of the sheet names:

J1 = GMP
J2 = GMT
J3 = FMP
J4 = TRN

Name that list something like SList.

This will work as long as the table array is the same on each sheet.

Enetered as an array using the key combo of CTRL,SHIFT,ENTER:

=VLOOKUP(A1,INDIRECT("'"&INDEX(SList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SList&"'!A1:A10"),A1)>0,0))&"'!A1:C10"),2,0)

A1 is the lookup value

This is how that formula would look as it operates on a single sheet:

=VLOOKUP(A1,GMP!A1:C10,2,0)

Biff

"Hirsch" <[email protected]> wrote in message
news:[email protected]...
> Is there a formula to incorporate a vlookup scanning multiple tabs.
>
> i.e.
> Total Tab will scan "GMP" "GMT" "FMP" & "TRN"
>
> returning #n/a for the missing information and the first line item found
> on
> the rest.
>
> Thanks in advance.

Hi!

Make a list of the sheet names:

J1 = GMP
J2 = GMT
J3 = FMP
J4 = TRN

Name that list something like SList.

This will work as long as the table array is the same on each sheet.

Enetered as an array using the key combo of CTRL,SHIFT,ENTER:

=VLOOKUP(A1,INDIRECT("'"&INDEX(SList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SList&"'!A1:A10"),A1)>0,0))&"'!A1:C10"),2,0)

A1 is the lookup value

This is how that formula would look as it operates on a single sheet:

=VLOOKUP(A1,GMP!A1:C10,2,0)

Biff

"Hirsch" <[email protected]> wrote in message
news:[email protected]...
> Is there a formula to incorporate a vlookup scanning multiple tabs.
>
> i.e.
> Total Tab will scan "GMP" "GMT" "FMP" & "TRN"
>
> returning #n/a for the missing information and the first line item found
> on
> the rest.
>
> Thanks in advance.

Hi!

Make a list of the sheet names:

J1 = GMP
J2 = GMT
J3 = FMP
J4 = TRN

Name that list something like SList.

This will work as long as the table array is the same on each sheet.

Enetered as an array using the key combo of CTRL,SHIFT,ENTER:

=VLOOKUP(A1,INDIRECT("'"&INDEX(SList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SList&"'!A1:A10"),A1)>0,0))&"'!A1:C10"),2,0)

A1 is the lookup value

This is how that formula would look as it operates on a single sheet:

=VLOOKUP(A1,GMP!A1:C10,2,0)

Biff

"Hirsch" <[email protected]> wrote in message
news:[email protected]...
> Is there a formula to incorporate a vlookup scanning multiple tabs.
>
> i.e.
> Total Tab will scan "GMP" "GMT" "FMP" & "TRN"
>
> returning #n/a for the missing information and the first line item found
> on
> the rest.
>
> Thanks in advance.