looking up info in a sheet

Karleen26

New Member
Joined
Jul 28, 2007
Messages
5
I have no idea of how to do want I want.

I have a workbook with 2 sheets. Sheet 1 is name quote and sheet 2 is named vendor price.

SO vendor price is a table Column A is a list of items the vendors colors and Row 1 is all the sizes, and the rest of the cells has the pricing to match each size and color.

There are 140 rows and 27 columns.

Then on sheet quote I made drop down list in A1 and B1. A1 has Color and B1 and Size, now in C1 I want it to automatically show the price that would be in the matching cell on the table I used to create the drop down lists.

I just don't know how to get A1 + B1 to look up the matching cell.

Any help would be great!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,261
Office Version
  1. 365
Platform
  1. Windows
Karleen26

Welcome to the MrExcel board!

See if this smaller version is what you want? If so, you should be able to extend it to suit your actual circumstances.

Excel Workbook
ABCDEFGH
1Colour/Size456789
2Red2.002.503.003.504.005.00
3Blue2.752.852.953.053.153.25
4Green3.005.007.009.0011.0013.00
5Yellow2.002.503.505.007.009.50
6
Vendor Price



Excel Workbook
ABCD
1Blue83.15
2
Quote
 

Karleen26

New Member
Joined
Jul 28, 2007
Messages
5
When I type it in sheet this is what happens :(

=INDEX('Vendor Price'!#REF!,MATCH(#REF!,'Vendor Price'!#REF!,0),MATCH(#REF!,'Vendor Price'!#REF!,0))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,261
Office Version
  1. 365
Platform
  1. Windows
When I type it in sheet this is what happens :(

=INDEX('Vendor Price'!#REF!,MATCH(#REF!,'Vendor Price'!#REF!,0),MATCH(#REF!,'Vendor Price'!#REF!,0))
Try copying and pasting from my formula above, rather than trying to type it.

Also, if you are not already, try setting up a new file just the way I have to see if you can get it working. Then you will have a better chance of understanding what is happening and applying it to your own sheet.

Edit: Also, are you using a standard English version of Excel?
 

Karleen26

New Member
Joined
Jul 28, 2007
Messages
5
OK,

SO when I entered everything the exact same way as you had in the example you sent me it works.

So what part do I need to change if I want to put it in a different cell
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,261
Office Version
  1. 365
Platform
  1. Windows
OK,

SO when I entered everything the exact same way as you had in the example you sent me it works.

So what part do I need to change if I want to put it in a different cell
Putting the formula in a different cell makes no difference - what is important is that your formula is referring to the correct sheet names, cells and ranges. So, things to check:

Is the sheet name 'Vendor Price' and not say 'VendorPrice'?
The A1:G5 in my formula needs to refer to the range that contains your whole color/size table in the 'Vendor price' sheet.
The ..MATCH(A1.. needs to refer to the cell that contains your colour data validation (in the 'Quote' sheet)
The ...MATCH(B1.. needs to refer to the cell that contains your size data validation (in the 'Quote' sheet)
etc
 

Forum statistics

Threads
1,181,102
Messages
5,928,069
Members
436,586
Latest member
latintxn

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