looking up info in a sheet

[Karleen26]

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!

 

[Peter_SSs]

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
	A	B	C	D	E	F	G	H
1	Colour/Size	4	5	6	7	8	9
2	Red	2.00	2.50	3.00	3.50	4.00	5.00
3	Blue	2.75	2.85	2.95	3.05	3.15	3.25
4	Green	3.00	5.00	7.00	9.00	11.00	13.00
5	Yellow	2.00	2.50	3.50	5.00	7.00	9.50
6
Vendor Price

Excel Workbook
	A	B	C	D
1	Blue	8	3.15
2
Quote

 

[Karleen26]

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]

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]

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]

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