How do I find a price in another workbook

Maleioch

New Member
Joined
Oct 11, 2002
Messages
12
Okay, I have a workbook that I keep pricing for items in. Lets call it Fixture Price List. Now in that workbook I have worksheets, they are named faucets/tubs/toilets etc. on each sheet I have model names/numbers with the cost of that model to the right of the model name/number. Similar to this: Col A would be brand A models and Col B has the corresponding price. then I skip to Col D with another brand list and Col E with the corresponding price. With me so far? Okay, how can I get my pricing template to look in that workbook, on that worksheet for the price of the model I enter. Say I enter on my template in cell B7 "bigtub". Now I want cell C7 to display the price of the item I entered in B7 by searching the worksheet "tubs" in the workbook "fixtures". I can't use VLookup cause it wants to look in only 1 col and then return a value in the next col. How do I get excel to search that whole worksheet for the match of cell B7 then return the amount that is listed next to it?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hello and welcome to the board Maleioch

If I read you info correctly, I think the way you have structured your layout of data ...ie horizontally vs. vertically makes the lookup, match, index features more difficult to look up the data that you want.


If the data was more in a simple vertical (data base) alignment it might make the lookup task a little easier. Anyone else care to voice an another opinion or suggest a solution with the current alignment of data.

pll
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
On 2002-10-12 19:44, Maleioch wrote:
Okay, I have a workbook that I keep pricing for items in. Lets call it Fixture Price List. Now in that workbook I have worksheets, they are named faucets/tubs/toilets etc. on each sheet I have model names/numbers with the cost of that model to the right of the model name/number. Similar to this: Col A would be brand A models and Col B has the corresponding price. then I skip to Col D with another brand list and Col E with the corresponding price. With me so far? Okay, how can I get my pricing template to look in that workbook, on that worksheet for the price of the model I enter. Say I enter on my template in cell B7 "bigtub". Now I want cell C7 to display the price of the item I entered in B7 by searching the worksheet "tubs" in the workbook "fixtures". I can't use VLookup cause it wants to look in only 1 col and then return a value in the next col. How do I get excel to search that whole worksheet for the match of cell B7 then return the amount that is listed next to it?

Hi Maleioch:

In addition to what plettieri said, your set up can work -- and you can make your formulas more meaningful and easier to remember by giving your data sets for Brand_Model | Price ranges appropriate NAMEs. I have reproduced your situation with the pricing data in a workbook called Fixtures, then in the workbook called FixturePrices, I pull up the data for prices of various items from the worksheets in the Fixtures workbook. This would be best described by the following simulation
FixturePrices.xls
ABCD
1TubsFaucets
2Brand_ModelPriceBrand_ModelPrice
3BigTub301TheFaucet31
4TheTub401MediumFaucet21
5?Tub#N/ABigFaucet31
6SmallTub101UnknownFaucet#N/A
Sheet1
</SPAN>

Please post back and tell us whether it works for you -- otherwise explain a little further and let us take it from there.

Regards!

Yogi
 

Maleioch

New Member
Joined
Oct 11, 2002
Messages
12
Thanks for the quick responses.

I see your example, and that is close to what I have now. What I was trying to accomplish was to have it look through a whole worksheet for the matching tub brand and return the price. So in your example I would want the first column to be Tub brand A and the third column to be Tub brand B...is it possible to have a formula that would look through both Columns to find the match and then return the price?...don't know if this will work:

Tub Brand A.....price....tub Brand B...price
fat tub.............50...........best tub.......20
skinny tub..........30........worst tub........15
This message was edited by Maleioch on 2002-10-13 09:41
This message was edited by Maleioch on 2002-10-13 09:41
This message was edited by Maleioch on 2002-10-13 09:42
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

Hi Maleioch:

Since your data layout is well structured, you can name the ranges to be used in the VLOOKUP formulas (to correspond in someway to the LOOKUP value) -- so you can do the whole thing with formulas -- without having to use MACROs or PROCEDUREs. Personally, I feel even though MACROs and PROCEDUREs can add powerful capabilities in an application, the best solution would be a solution with no MACROs -- meaning every thing else being equla, I would prefer formulatic approach over procedural.

Regards!

Yogi
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Maleioch:

See my worksheet simulation where my VLOOKUP formulation spans two fifferent LOOKUP_RANGEs
Book1
ABCDEFG
1TubBrandApriceTubBrandBprice
2fattub50besttub20besttub20
3skinnytub30worsttub15skinnytub30
4shinytub65hottub275hottub275
5fulltub93halftub31fattub50
6LOOKUP_RANGE(sourcedata)LOOKUP_VALUES
7
8
Sheet2
</SPAN>

Regards!

Yogi
 

Maleioch

New Member
Joined
Oct 11, 2002
Messages
12

ADVERTISEMENT

Your last formula works...but..it is limited to two groups (columns or tables). I used it in this fashion:

=IF(ISNA(VLOOKUP(B7,'[NW Fixture Price List.xls]Tub & Showers'!$A:$B,2,FALSE)),VLOOKUP(B7,'[NW Fixture Price List.xls]Tub & Showers'!$D:$E,2,FALSE),VLOOKUP(B7,'[NW Fixture Price List.xls]Tub & Showers'!$A:$B,2,FALSE))

This one above worked by the way...thanks


How can I use it in this fashion and on..is there a limit to how many columns it will search?

=IF(ISNA(VLOOKUP(B7,'[NW Fixture Price List.xls]Tub & Showers'!$A:$B,2,FALSE)),VLOOKUP(B7,'[NW Fixture Price List.xls]Tub & Showers'!$D:$E,2,FALSE),VLOOKUP(B7,'[NW Fixture Price List.xls]Tub & Showers'!$G:$H,2,FALSE),VLOOKUP(B7,'[NW Fixture Price List.xls]Tub & Showers'!$A:$B,2,FALSE)).....etc...
This message was edited by Maleioch on 2002-10-13 12:57
 

Maleioch

New Member
Joined
Oct 11, 2002
Messages
12
I wonder if it would be easier for me to E-mail you my two files and let you see what I am trying to accomplish??

It's driving me crazy!!!!

:)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-10-13 12:55, Maleioch wrote:
Your last formula works...but..it is limited to two groups (columns or tables). I used it in this fashion:

=IF(ISNA(VLOOKUP(B7,'[NW Fixture Price List.xls]Tub & Showers'!$A:$B,2,FALSE)),VLOOKUP(B7,'[NW Fixture Price List.xls]Tub & Showers'!$D:$E,2,FALSE),VLOOKUP(B7,'[NW Fixture Price List.xls]Tub & Showers'!$A:$B,2,FALSE))

This one above worked by the way...thanks


How can I use it in this fashion and on..is there a limit to how many columns it will search?

=IF(ISNA(VLOOKUP(B7,'[NW Fixture Price List.xls]Tub & Showers'!$A:$B,2,FALSE)),VLOOKUP(B7,'[NW Fixture Price List.xls]Tub & Showers'!$D:$E,2,FALSE),VLOOKUP(B7,'[NW Fixture Price List.xls]Tub & Showers'!$G:$H,2,FALSE),VLOOKUP(B7,'[NW Fixture Price List.xls]Tub & Showers'!$A:$B,2,FALSE)).....etc...
This message was edited by Maleioch on 2002-10-13 12:57

Does what you're looking up occur just once in the lookup table?
This message was edited by Aladin Akyurek on 2002-10-13 13:18
 

Maleioch

New Member
Joined
Oct 11, 2002
Messages
12
Yes, Just once. On the worksheet there is only one version of a model # even from a different manufacturer.
 

Forum statistics

Threads
1,148,277
Messages
5,745,813
Members
423,980
Latest member
zimza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top