Hi,

I have a problem using VLOOKUP.

I have 7 sheets of data listing product codes and product descriptions

I need to write a VLOOKUP based upon cell A1 where the contract name is
located, and Cell B1 where the product code is located.

The product code can be repeated for different contracts but listing
different products hterebye stopping me from creating 1 sheet.

Is there a way of writing a VLOOKUP so that the product description appears
in cell C1 based upon the information in cell A1 and B1? In other words, so
that the VLOOKUP in cell C1 goes to the correct sheet for the contract.

Many thanks

Hi,
This is a reply from Peo Sjoblom on looking up values over mutiple
worksheets.

Hopefully you can adapt it to your need. It wasn't clear to me what the
relevance of the contract number is ... don't you only require the product
code from B1?

HTH

-----------------------------------------------------------------------------------------------------
If you have the lookup value in A2 on a summary sheet and the sheets you
want to lookup are Sheet1:Sheet8,
the table is A1:C200 and you want to return the value in the second column
(B)


=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2"; "Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Shee t8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3"; "Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A 1:A200"),A2)0),0))&"'!A1:C200"),2,0)

entered with ctrl + shift & enter

if you put all sheet names in a range of cells and give it a name it is less
ugly

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A1:A200"),A2)0) ,0))&"'!A1:C200"),2,0)

where MySheets would hold the names



"Dr Phibes" wrote:

Hi,

I have a problem using VLOOKUP.

I have 7 sheets of data listing product codes and product descriptions

I need to write a VLOOKUP based upon cell A1 where the contract name is
located, and Cell B1 where the product code is located.

The product code can be repeated for different contracts but listing
different products hterebye stopping me from creating 1 sheet.

Is there a way of writing a VLOOKUP so that the product description appears
in cell C1 based upon the information in cell A1 and B1? In other words, so
that the VLOOKUP in cell C1 goes to the correct sheet for the contract.

Many thanks

"Dr Phibes" wrote:
I have 7 sheets of data listing product codes
and product descriptions

I need to write a VLOOKUP based upon cell A1
where the contract name is
located, and Cell B1 where the product code is located.

The product code can be repeated for different contracts
but listing different products thereby stopping me
from creating 1 sheet.

Is there a way of writing a VLOOKUP
so that the product description appears
in cell C1 based upon the information in cell A1 and B1?
In other words, so that the VLOOKUP in cell C1
goes to the correct sheet for the contract.

One way ..

Assuming that in a new sheet,
the sheetnames are listed in A1 down,
and in B1 down are the product codes
(sheetnames are assumed to be the contract names)

and in the 7 contract sheets,
the data is housed within cols A and B
Col A = product code, Col B = product descriptions

we could put in C1:
=VLOOKUP(B1,INDIRECT("'"&A1&"'!A:B"),2,0)
and copy down to return the product description
from the correct contract sheet (amongst the 7)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Many thanks to both of you.

Sheet is now working correctly

"Dr Phibes" wrote:
Many thanks to both of you.
Sheet is now working correctly

Glad to hear that, and thanks for feedback
(quite a rare phenomena of late <g)

For thread completeness,
perhaps you could also indicate
which response worked for you (or both) ?
[it doesn't matter if it isn't mine]
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hi Max. yes, it was your solution. I tried that one first, merely because it
had less things in it to go wrong. I would like to thank Toppers too, both of
you responded quickly and gave me a number of options to solve my problem.

As for feedback. I always think that, if you say thank you, then you can ask
again next time :)

"Dr Phibes" wrote:
Hi Max. yes, it was your solution.
I tried that one first, merely because it had less things
in it to go wrong. I would like to thank Toppers too,
both of you responded quickly and gave me a number
of options to solve my problem.

no prob .. even if it wasn't my response that worked <g

As for feedback. I always think that,
if you say thank you, then you can ask
again next time :)

imo, at the very least, feeding back to
responders provides closure to the post,
and for the responders, too !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---