LOOKUP or VLOOKUP

M

MichelleS

I need help with LOOKUP or VLOOKUP.

I have two EXCEL 2000 documents ( Estimates.xls and Prices.xls).

(this is a snapshot of ESTIMATES.xls)
A B C
1
2 Product Description Est Price
3 400 _____ $____
4 600 _____ $____
5 900 _____ $____

(this is a snapshot of PRICES.xls, Tab or Sheet 1)
A B C
1 Product Description Price
2 400 Red XYZ $47.00
3 500 Blue SPQ $68.00

(this is a snapshot of PRICES.xls, Tab or Sheet 2)
A B C
1 Product Description Price
2 600 Green XYZ $40.00
3 800 Brown SPQ $88.00

(this is a snapshot of PRICES.xls, Tab or Sheet 3)
A B C
1 Product Description Price
2 900 Black XYZ $70.00
3 950 White SPQ $38.00

I would like to type a product code into cell A3 on ESTIMATES.xls and have
it automatically populate cells B3 and C3 with info from the PRICE.xls. The
PRICE.xls has 3 tabs or sheets that need to be searched in order to populate
correctly.

Any help with this is appreciated.
(ps: I'm a beginner so please use details in wording)
Michelle
 
S

shail

Hi Michelle,

Enter the formula at B3 as below

=IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!A$1:B$3,2,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!A$1:B$3,2,FALSE))

Enter this pressing keys <CTRL><SHIFT><ENTER> and braces {} will appear
around the formula.

Copy down till B5. And for next column that is C, copy the formula and
make changes as below:

=IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!A$1:B$3,3,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!A$1:B$3,3,FALSE))


I hope this works for you. Do send me the feedback to help you over
this if any error occur.

For now, do it for just two sheets. If this is working for you I will
send you the formula for three sheet (tabs)


Thanks,

Shail
 
S

shail

Use this formula for column 2 -

=IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!$A$1:$B­$3,2,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!$A$1:$B$3,2,FALSE))

and for column 3 -

=IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!$A$1:$B­$3,3,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!$A$1:$B$3,3,FALSE))


Ignore "dash" ( - ), if they appear inside the formula.

thanks again

Shail

Hi Michelle,

Enter the formula at B3 as below

=IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!A$1:B$3,2,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!A$1:B$3,2,FALSE))

Enter this pressing keys <CTRL><SHIFT><ENTER> and braces {} will appear
around the formula.

Copy down till B5. And for next column that is C, copy the formula and
make changes as below:

=IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!A$1:B$3,3,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!A$1:B$3,3,FALSE))


I hope this works for you. Do send me the feedback to help you over
this if any error occur.

For now, do it for just two sheets. If this is working for you I will
send you the formula for three sheet (tabs)


Thanks,

Shail


I need help with LOOKUP or VLOOKUP.

I have two EXCEL 2000 documents ( Estimates.xls and Prices.xls).

(this is a snapshot of ESTIMATES.xls)
A B C
1
2 Product Description Est Price
3 400 _____ $____
4 600 _____ $____
5 900 _____ $____

(this is a snapshot of PRICES.xls, Tab or Sheet 1)
A B C
1 Product Description Price
2 400 Red XYZ $47.00
3 500 Blue SPQ $68.00

(this is a snapshot of PRICES.xls, Tab or Sheet 2)
A B C
1 Product Description Price
2 600 Green XYZ $40.00
3 800 Brown SPQ $88.00

(this is a snapshot of PRICES.xls, Tab or Sheet 3)
A B C
1 Product Description Price
2 900 Black XYZ $70.00
3 950 White SPQ $38.00

I would like to type a product code into cell A3 on ESTIMATES.xls and have
it automatically populate cells B3 and C3 with info from the PRICE.xls.The
PRICE.xls has 3 tabs or sheets that need to be searched in order to populate
correctly.

Any help with this is appreciated.
(ps: I'm a beginner so please use details in wording)
Michelle
Click to expand...
Click to expand...
 
M

MichelleS

SHAIL,

You are a life saver....THANK YOU !!

I have 4 more tabs or sheets in that same document, should I continue the
formula string to include those tabs? Example, you gave me the formula for
the first 2 tabs, now would that same IF(OR........ formula work, if I add on
4 more VLOOKUPs??


shail said:
Use this formula for column 2 -

=IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!$A$1:$B­$3,2,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!$A$1:$B$3,2,FALSE))

and for column 3 -

=IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!$A$1:$B­$3,3,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!$A$1:$B$3,3,FALSE))


Ignore "dash" ( - ), if they appear inside the formula.

thanks again

Shail

Hi Michelle,

Enter the formula at B3 as below

=IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!A$1:B$3,2,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!A$1:B$3,2,FALSE))

Enter this pressing keys <CTRL><SHIFT><ENTER> and braces {} will appear
around the formula.

Copy down till B5. And for next column that is C, copy the formula and
make changes as below:

=IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!A$1:B$3,3,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!A$1:B$3,3,FALSE))


I hope this works for you. Do send me the feedback to help you over
this if any error occur.

For now, do it for just two sheets. If this is working for you I will
send you the formula for three sheet (tabs)


Thanks,

Shail


I need help with LOOKUP or VLOOKUP.

I have two EXCEL 2000 documents ( Estimates.xls and Prices.xls).

(this is a snapshot of ESTIMATES.xls)
A B C
1
2 Product Description Est Price
3 400 _____ $____
4 600 _____ $____
5 900 _____ $____

(this is a snapshot of PRICES.xls, Tab or Sheet 1)
A B C
1 Product Description Price
2 400 Red XYZ $47.00
3 500 Blue SPQ $68.00

(this is a snapshot of PRICES.xls, Tab or Sheet 2)
A B C
1 Product Description Price
2 600 Green XYZ $40.00
3 800 Brown SPQ $88.00

(this is a snapshot of PRICES.xls, Tab or Sheet 3)
A B C
1 Product Description Price
2 900 Black XYZ $70.00
3 950 White SPQ $38.00

I would like to type a product code into cell A3 on ESTIMATES.xls and have
it automatically populate cells B3 and C3 with info from the PRICE.xls. The
PRICE.xls has 3 tabs or sheets that need to be searched in order to populate
correctly.

Any help with this is appreciated.
(ps: I'm a beginner so please use details in wording)
Michelle
Click to expand...
Click to expand...
Click to expand...
 
S

shail

Hi Michelle,

Thanks for the feedback. Yes, you can use IF & OR for next tabs. I have
made this as below.

=IF(OR([Prices.xls]sheet1!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet1!$A$1:$B$5,2,FALSE),IF(OR([Prices.xls]sheet2!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet2!$A$1:$B$5,2,FALSE),IF(OR([Prices.xls]sheet3!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet3!$A$1:$B$5,2,FALSE),IF(OR([Prices.xls]sheet4!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet4!$A$1:$B$5,2,FALSE),""))))


Hope that too work.

Thanks,

Shail

SHAIL,

You are a life saver....THANK YOU !!

I have 4 more tabs or sheets in that same document, should I continue the
formula string to include those tabs? Example, you gave me the formula for
the first 2 tabs, now would that same IF(OR........ formula work, if I add on
4 more VLOOKUPs??


shail said:
Use this formula for column 2 -

=IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!$A$1:$B­$3,2,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!$A$1:$B$3,2,FALSE))

and for column 3 -

=IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!$A$1:$B­$3,3,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!$A$1:$B$3,3,FALSE))


Ignore "dash" ( - ), if they appear inside the formula.

thanks again

Shail

Hi Michelle,

Enter the formula at B3 as below

=IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!A$1:B$3,2,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!A$1:B$3,2,FALSE))

Enter this pressing keys <CTRL><SHIFT><ENTER> and braces {} will appear
around the formula.

Copy down till B5. And for next column that is C, copy the formula and
make changes as below:

=IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!A$1:B$3,3,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!A$1:B$3,3,FALSE))


I hope this works for you. Do send me the feedback to help you over
this if any error occur.

For now, do it for just two sheets. If this is working for you I will
send you the formula for three sheet (tabs)


Thanks,

Shail



MichelleS wrote:
I need help with LOOKUP or VLOOKUP.

I have two EXCEL 2000 documents ( Estimates.xls and Prices.xls).

(this is a snapshot of ESTIMATES.xls)
A B C
1
2 Product Description Est Price
3 400 _____ $____
4 600 _____ $____
5 900 _____ $____

(this is a snapshot of PRICES.xls, Tab or Sheet 1)
A B C
1 Product Description Price
2 400 Red XYZ $47.00
3 500 Blue SPQ $68.00

(this is a snapshot of PRICES.xls, Tab or Sheet 2)
A B C
1 Product Description Price
2 600 Green XYZ $40.00
3 800 Brown SPQ $88.00

(this is a snapshot of PRICES.xls, Tab or Sheet 3)
A B C
1 Product Description Price
2 900 Black XYZ $70.00
3 950 White SPQ $38.00

I would like to type a product code into cell A3 on ESTIMATES.xls and have
it automatically populate cells B3 and C3 with info from the PRICE.xls. The
PRICE.xls has 3 tabs or sheets that need to be searched in order topopulate
correctly.

Any help with this is appreciated.
(ps: I'm a beginner so please use details in wording)
Michelle
Click to expand...
Click to expand...
Click to expand...
 
M

MichelleS

Shail,

You are SUPERB, THANKS A TRILLION, but..........
Your last formula only works for sheet1 and sheet2. It did not work for
sheet3, sheet4, sheet5 and sheet6. Example: When I type a product code from
sheet5 in cell A3 on ESTIMATE.xls, then the cell B3 comes back with an empty
cell. But if I type a product code from sheet1 or sheet2 it works. See my
formula below:

IF(OR('[Prices.xls]sheet1'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet1'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet2'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet2'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet3'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet3'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet4'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet4'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet5'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet5'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet6'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet6'!$A$1:$B$3,2,FALSE),""))))))

I did hit Control,Shift,Enter at the same time after the last paranthese.
Am I missing a comma, letter,quote, etc. ??

Please HELP !!
VeryVeryVery Much Appreciative,
Michelle


shail said:
Hi Michelle,

Thanks for the feedback. Yes, you can use IF & OR for next tabs. I have
made this as below.

=IF(OR([Prices.xls]sheet1!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet1!$A$1:$B$5,2,FALSE),IF(OR([Prices.xls]sheet2!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet2!$A$1:$B$5,2,FALSE),IF(OR([Prices.xls]sheet3!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet3!$A$1:$B$5,2,FALSE),IF(OR([Prices.xls]sheet4!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet4!$A$1:$B$5,2,FALSE),""))))


Hope that too work.
Thanks,
Shail
SHAIL,
You are a life saver....THANK YOU !!
Click to expand...
Click to expand...
 
S

shail

hi again Michelle,

You might need to increase the range of your data selection. I mean
here you have A1 till A3, I guess you should increase it from A3 to
your requirement. Hope this works for you, as the formula you have
written is just perfect.

Otherwise do send me the excel file over my email address. It is
shaildeogam at gmail.com


Thanks again for the feedback.

Shail


Shail,

You are SUPERB, THANKS A TRILLION, but..........
Your last formula only works for sheet1 and sheet2. It did not work for
sheet3, sheet4, sheet5 and sheet6. Example: When I type a product code from
sheet5 in cell A3 on ESTIMATE.xls, then the cell B3 comes back with an empty
cell. But if I type a product code from sheet1 or sheet2 it works. See my
formula below:

IF(OR('[Prices.xls]sheet1'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet1'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet2'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet2'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet3'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet3'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet4'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet4'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet5'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet5'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet6'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet6'!$A$1:$B$3,2,FALSE),""))))))

I did hit Control,Shift,Enter at the same time after the last paranthese.
Am I missing a comma, letter,quote, etc. ??

Please HELP !!
VeryVeryVery Much Appreciative,
Michelle


shail said:
Hi Michelle,

Thanks for the feedback. Yes, you can use IF & OR for next tabs. I have
made this as below.

=IF(OR([Prices.xls]sheet1!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet1!$A$1:$B$5,2,FALSE),IF(OR([Prices.xls]sheet2!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet2!$A$1:$B$5,2,FALSE),IF(OR([Prices.xls]sheet3!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet3!$A$1:$B$5,2,FALSE),IF(OR([Prices.xls]sheet4!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet4!$A$1:$B$5,2,FALSE),""))))


Hope that too work.
Thanks,
Shail
SHAIL,
You are a life saver....THANK YOU !!

I have two EXCEL 2000 documents ( Estimates.xls and Prices.xls).

(this is a snapshot of ESTIMATES.xls)
A B C
1
2 Product Description Est Price
3 400 _____ $____
4 600 _____ $____
5 900 _____ $____

(this is a snapshot of PRICES.xls, Tab or Sheet 1)
A B C
1 Product Description Price
2 400 Red XYZ $47.00
3 500 Blue SPQ $68.00

(this is a snapshot of PRICES.xls, Tab or Sheet 2)
A B C
1 Product Description Price
2 600 Green XYZ $40.00
3 800 Brown SPQ $88.00

(this is a snapshot of PRICES.xls, Tab or Sheet 3)
A B C
1 Product Description Price
2 900 Black XYZ $70.00
3 950 White SPQ $38.00

I would like to type a product code into cell A3 on ESTIMATES.xls and have
it automatically populate cells B3 and C3 with info from the PRICE.xls. The
PRICE.xls has 3 tabs or sheets that need to be searched in order to populate
correctly.

Any help with this is appreciated.
(ps: I'm a beginner so please use details in wording)
Michelle
Click to expand...
Click to expand...
Click to expand...
 
M

MichelleS

OK, I did it, still the same output.......OK, I promise this is the last
question...Shail, my data is not all in ascending order. I forgot with
VLOOKUP, does all of your data have to be in ascending order ?? That may be
my problem, but I cannot change the order, it will change my dollars. So, is
there a brute force or cheat way to overlook the non-ascending data?

shail said:
hi again Michelle,

You might need to increase the range of your data selection. I mean
here you have A1 till A3, I guess you should increase it from A3 to
your requirement. Hope this works for you, as the formula you have
written is just perfect.

Otherwise do send me the excel file over my email address. It is
shaildeogam at gmail.com


Thanks again for the feedback.

Shail


Shail,
You are SUPERB, THANKS A TRILLION, but..........
Your last formula only works for sheet1 and sheet2. It did not work for
sheet3, sheet4, sheet5 and sheet6. Example: When I type a product code from
sheet5 in cell A3 on ESTIMATE.xls, then the cell B3 comes back with an empty
cell. But if I type a product code from sheet1 or sheet2 it works. See my
formula below:

IF(OR('[Prices.xls]sheet1'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet1'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet2'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet2'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet3'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet3'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet4'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet4'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet5'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet5'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet6'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet6'!$A$1:$B$3,2,FALSE),""))))))
Please HELP !!
VeryVeryVery Much Appreciative,
Michelle
(this is a snapshot of ESTIMATES.xls)
A B C
1
2 Product Description Est Price
3 400 _____ $____
4 600 _____ $____
5 900 _____ $____

(this is a snapshot of PRICES.xls, Tab or Sheet 1)
A B C
1 Product Description Price
2 400 Red XYZ $47.00
3 500 Blue SPQ $68.00

(this is a snapshot of PRICES.xls, Tab or Sheet 2)
A B C
1 Product Description Price
2 600 Green XYZ $40.00
3 800 Brown SPQ $88.00

(this is a snapshot of PRICES.xls, Tab or Sheet 3)
A B C
1 Product Description Price
2 900 Black XYZ $70.00
3 950 White SPQ $38.00

I would like to type a product code into cell A3 on ESTIMATES.xls and have
it automatically populate cells B3 and C3 with info from the PRICE.xls. The
PRICE.xls has 3 tabs or sheets that need to be searched in order to populate
correctly.

Any help with this is appreciated.
(ps: I'm a beginner so please use details in wording)
Michelle
Click to expand...
Click to expand...
Click to expand...
 
S

shail

No Michelle, it is not necessary to have your data to be ascending,
descending or random. VLOOKUP will find its match, no matter how your
data is. If you could have sent me the excel file I might have tried to
fix your problem.

And do feel free to ask questions anytime, I feel nice to help with the
knowledege I have gained so far. I too am in a learning phase, I too
keep asking the questions in this forum and get the answers.


Thanks again.

Shail

OK, I did it, still the same output.......OK, I promise this is the last
question...Shail, my data is not all in ascending order. I forgot with
VLOOKUP, does all of your data have to be in ascending order ?? That may be
my problem, but I cannot change the order, it will change my dollars. So, is
there a brute force or cheat way to overlook the non-ascending data?

shail said:
hi again Michelle,

You might need to increase the range of your data selection. I mean
here you have A1 till A3, I guess you should increase it from A3 to
your requirement. Hope this works for you, as the formula you have
written is just perfect.

Otherwise do send me the excel file over my email address. It is
shaildeogam at gmail.com


Thanks again for the feedback.

Shail


Shail,
You are SUPERB, THANKS A TRILLION, but..........
Your last formula only works for sheet1 and sheet2. It did not work for
sheet3, sheet4, sheet5 and sheet6. Example: When I type a product code from
sheet5 in cell A3 on ESTIMATE.xls, then the cell B3 comes back with an empty
cell. But if I type a product code from sheet1 or sheet2 it works. See my
formula below:

IF(OR('[Prices.xls]sheet1'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet1'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet2'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet2'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet3'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet3'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet4'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet4'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet5'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet5'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet6'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet6'!$A$1:$B$3,2,FALSE),""))))))
Please HELP !!
VeryVeryVery Much Appreciative,
Michelle
(this is a snapshot of ESTIMATES.xls)
A B C
1
2 Product Description Est Price
3 400 _____ $____
4 600 _____ $____
5 900 _____ $____

(this is a snapshot of PRICES.xls, Tab or Sheet 1)
A B C
1 Product Description Price
2 400 Red XYZ $47.00
3 500 Blue SPQ $68.00

(this is a snapshot of PRICES.xls, Tab or Sheet 2)
A B C
1 Product Description Price
2 600 Green XYZ $40.00
3 800 Brown SPQ $88.00

(this is a snapshot of PRICES.xls, Tab or Sheet 3)
A B C
1 Product Description Price
2 900 Black XYZ $70.00
3 950 White SPQ $38.00

I would like to type a product code into cell A3 on ESTIMATES.xls and have
it automatically populate cells B3 and C3 with info from the PRICE.xls. The
PRICE.xls has 3 tabs or sheets that need to be searched in order to populate
correctly.

Any help with this is appreciated.
(ps: I'm a beginner so please use details in wording)
Michelle
Click to expand...
Click to expand...
Click to expand...
 
M

MichelleS

Hi Again Shail,

I forwarded you an email yesterday at your e-address
([email protected]), titled 'One of Your Microsoft Office Online
Students'. I hope you receive it, if not please let me know.

Thank you a bunch,
Michelle

shail said:
No Michelle, it is not necessary to have your data to be ascending,
descending or random. VLOOKUP will find its match, no matter how your
data is. If you could have sent me the excel file I might have tried to
fix your problem.

And do feel free to ask questions anytime, I feel nice to help with the
knowledege I have gained so far. I too am in a learning phase, I too
keep asking the questions in this forum and get the answers.


Thanks again.

Shail

OK, I did it, still the same output.......OK, I promise this is the last
question...Shail, my data is not all in ascending order. I forgot with
VLOOKUP, does all of your data have to be in ascending order ?? That may be
my problem, but I cannot change the order, it will change my dollars. So, is
there a brute force or cheat way to overlook the non-ascending data?

shail said:
hi again Michelle,

You might need to increase the range of your data selection. I mean
here you have A1 till A3, I guess you should increase it from A3 to
your requirement. Hope this works for you, as the formula you have
written is just perfect.

Otherwise do send me the excel file over my email address. It is
shaildeogam at gmail.com


Thanks again for the feedback.

Shail



MichelleS wrote:
Shail,
You are SUPERB, THANKS A TRILLION, but..........
Your last formula only works for sheet1 and sheet2. It did not work for
sheet3, sheet4, sheet5 and sheet6. Example: When I type a product code from
sheet5 in cell A3 on ESTIMATE.xls, then the cell B3 comes back with an empty
cell. But if I type a product code from sheet1 or sheet2 it works. See my
formula below:

IF(OR('[Prices.xls]sheet1'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet1'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet2'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet2'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet3'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet3'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet4'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet4'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet5'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet5'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet6'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet6'!$A$1:$B$3,2,FALSE),""))))))
Click to expand...
Please HELP !!
VeryVeryVery Much Appreciative,
Michelle
(this is a snapshot of ESTIMATES.xls)
A B C
1
2 Product Description Est Price
3 400 _____ $____
4 600 _____ $____
5 900 _____ $____

(this is a snapshot of PRICES.xls, Tab or Sheet 1)
A B C
1 Product Description Price
2 400 Red XYZ $47.00
3 500 Blue SPQ $68.00

(this is a snapshot of PRICES.xls, Tab or Sheet 2)
A B C
1 Product Description Price
2 600 Green XYZ $40.00
3 800 Brown SPQ $88.00

(this is a snapshot of PRICES.xls, Tab or Sheet 3)
A B C
1 Product Description Price
2 900 Black XYZ $70.00
3 950 White SPQ $38.00

I would like to type a product code into cell A3 on ESTIMATES.xls and have
it automatically populate cells B3 and C3 with info from the PRICE.xls. The
PRICE.xls has 3 tabs or sheets that need to be searched in order to populate
correctly.

Any help with this is appreciated.
(ps: I'm a beginner so please use details in wording)
Michelle
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

2 Workbooks and 6 Worksheets - Need to Populate 1 Workbook 15
PLEASE PLEASE INDEX MATCH OR SOMETHING ELSE ?? 0
Copy and shift data automaticaly 0
What type of "lookup" 3
¿How to divide a total into 0
Excel Lookup 1
To find rate of each item from item.xls and to copy price.xls 7
lookup two different variables 0

Top