software

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(quot;'quot;amp;INDEX({quot;Sheet1quot;;quot;Sheet2quot;; quot;Sheet3quot;;quot;Sheet4quot;;quot;Sheet5quot;;quot;Sheet6quot;;quot;Sheet7quot;;quot;Shee t8quot;},MATCH(1,--(COUNTIF(INDIRECT(quot;'quot;amp;{quot;Sheet1quot;;quot;Sheet2quot;;quot;Sheet3quot;; quot;Sheet4quot;;quot;Sheet5quot;;quot;Sheet6quot;;quot;Sheet7quot;;quot;Sheet8quot;}amp;quot;'!A 1:A200quot;),A2)gt;0),0))amp;quot;'!A1:C200quot;),2,0)

entered with ctrl shift amp; enter

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

=VLOOKUP(A2,INDIRECT(quot;'quot;amp;INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT(quot;'quot;amp;MySheetsamp;quot;'!A1:A200quot;),A2)gt;0) ,0))amp;quot;'!A1:C200quot;),2,0)

where MySheets would hold the names
quot;Dr Phibesquot; wrote:

gt; Hi,
gt;
gt; I have a problem using VLOOKUP.
gt;
gt; I have 7 sheets of data listing product codes and product descriptions
gt;
gt; I need to write a VLOOKUP based upon cell A1 where the contract name is
gt; located, and Cell B1 where the product code is located.
gt;
gt; The product code can be repeated for different contracts but listing
gt; different products hterebye stopping me from creating 1 sheet.
gt;
gt; Is there a way of writing a VLOOKUP so that the product description appears
gt; in cell C1 based upon the information in cell A1 and B1? In other words, so
gt; that the VLOOKUP in cell C1 goes to the correct sheet for the contract.
gt;
gt; Many thanks

quot;Dr Phibesquot; wrote:
gt; I have 7 sheets of data listing product codes
gt; and product descriptions
gt;
gt; I need to write a VLOOKUP based upon cell A1
gt; where the contract name is
gt; located, and Cell B1 where the product code is located.
gt;
gt; The product code can be repeated for different contracts
gt; but listing different products thereby stopping me
gt; from creating 1 sheet.
gt;
gt; Is there a way of writing a VLOOKUP
gt; so that the product description appears
gt; in cell C1 based upon the information in cell A1 and B1?
gt; In other words, so that the VLOOKUP in cell C1
gt; 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(quot;'quot;amp;A1amp;quot;'!A:Bquot;),2,0)
and copy down to return the product description
from the correct contract sheet (amongst the 7)
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
Many thanks to both of you.

Sheet is now working correctlyquot;Dr Phibesquot; wrote:
gt; Many thanks to both of you.
gt; Sheet is now working correctly

Glad to hear that, and thanks for feedback
(quite a rare phenomena of late lt;ggt;)

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
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 quot;Dr Phibesquot; wrote:
gt; Hi Max. yes, it was your solution.
gt; I tried that one first, merely because it had less things
gt; in it to go wrong. I would like to thank Toppers too,
gt; both of you responded quickly and gave me a number
gt; of options to solve my problem.

no prob .. even if it wasn't my response that worked lt;ggt;

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

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