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?
 
On 2002-10-13 13:30, Maleioch wrote:
Yes, Just once. On the worksheet there is only one version of a model # even from a different manufacturer.

The figure below shows the workbook with the lookup table...
NW Fixture Price List.xls
ABCDE
1
2tub110tub39.25
3tub212tub79
4tub413tub911
5tub511.2tub1010
6
Tub & Showers


Note that A2:D5 is named LTable using the Name Box on the Formula Bar).

The following shows how you can search for a lookup value in LTable...
aaLookup Maleioch.xls
ABCDE
6
7tub2112
8tub9311
9tub240NotFound
10
Sheet1


The formula in C7 is...

=SUMPRODUCT(('NW Fixture Price List.xls'!LTable=B7)*COLUMN('NW Fixture Price List.xls'!LTable))

The formula in D7 is...

=IF(C7,INDEX('NW Fixture Price List.xls'!LTable,MATCH(B7,INDEX('NW Fixture Price List.xls'!LTable,0,C7),0),C7+1),"Not Found")
 
Upvote 0

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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

Hi Maleioch:

Let me answer your question indirectly -- Excel allows us to have 7 levels of nesting in a formula.

Regards!

Yogi
 
Upvote 0
Aladin,

That is great. It may just work. However, How can we get the two formulas you have to work in one cell so I don't have to have a cell that shows the index reference? Is that possible. In other words: in B7 I enter tub9 and in C7 it does the search AND returns the cost of tub9. Instead of having C7 show the index reference then having to add another cell D7 to give the price...?? Do I make any sense? My wife usually doesn't think so..hehe.
 
Upvote 0
On 2002-10-14 09:33, Maleioch wrote:

Aladin,

That is great. It may just work. However, How can we get the two formulas you have to work in one cell so I don't have to have a cell that shows the index reference? Is that possible. In other words: in B7 I enter tub9 and in C7 it does the search AND returns the cost of tub9. Instead of having C7 show the index reference then having to add another cell D7 to give the price...?? Do I make any sense? My wife usually doesn't think so..hehe.

I strongly advise against combining the two, unless you're willing to install the morefunc.xll add-in, which would allow us to avoid computing the same thing twice.

Can't you just hide col B?

Addendum. The whole thing in a single formula becomes...

=IF(SETV(SUMPRODUCT(('NW Fixture Price List.xls'!LTable=B7)*COLUMN('NW Fixture Price List.xls'!LTable))),INDEX('NW Fixture Price List.xls'!LTable,MATCH(B7,INDEX('NW Fixture Price List.xls'!LTable,0,GETV()),0),GETV()+1),"Not Found")

using the SETV/GETV pair from morefunc, which is downloadable from:

http://longre.free.fr/english/index.html
This message was edited by Aladin Akyurek on 2002-10-14 11:04
 
Upvote 0
Thanks again for all of the help. I just put the one formula in another column and then hid the column. Other than that, the formula worked great and my sheets work great!! Thank you so much.

I did have one follow up question for you Aladin. That is: Is it possible to have the formula put N/A instead of #Ref! in the cell if I don't enter any data for it?
 
Upvote 0
On 2002-10-14 16:46, Maleioch wrote:
Thanks again for all of the help. I just put the one formula in another column and then hid the column. Other than that, the formula worked great and my sheets work great!! Thank you so much.

I did have one follow up question for you Aladin. That is: Is it possible to have the formula put N/A instead of #Ref! in the cell if I don't enter any data for it?

Can you specify what you mean by "Is it possible to have the formula put N/A instead of #Ref! in the cell if I don't enter any data for it?"
This message was edited by Aladin Akyurek on 2002-10-14 19:19
 
Upvote 0
I'm sorry. In your example above, if you were to put nothing in cell B8 then in cell D8 it would put #REF! because you didn't give enough data. Is it possible to have cell D8 return #n/a or just n/a instead of #REF!?
 
Upvote 0
On 2002-10-14 19:25, Maleioch wrote:
I'm sorry. In your example above, if you were to put nothing in cell B8 then in cell D8 it would put #REF! because you didn't give enough data. Is it possible to have cell D8 return #n/a or just n/a instead of #REF!?

If B8 empty (no lookup value entered), C8 will show zero and D8 "Not Found".

If B8 contains a formula generated "", C8 and D8 will show the same values as above.

So I'm not sure how you get #REF! in D8.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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
Back
Top