I'm trying to create a cutting list sheet on excel and I have my reference sheet on one tab then the cutting list on the other.
The reference sheet will have duplicate values in each column, for example column A will say: FLAT BAR in every row there is a flat bar, and in column B it will have the dimensions of the product, which will have several duplicates over the entire reference sheet. How would I write the code to check whether both are true in a row and output the G column value for that row?
I have tried this formula: =INDEX('Reference Sheet'!A3:G100,MATCH(A4,'Reference Sheet'!A3:G100,0),MATCH('Order Sheet'!B4,'Reference Sheet'!A3:G100,0))
But I get "A value is not available", likely because the match formulas are not working together but rather returning multiple values each.
After getting a suggestion I tried the formula: {=INDEX('Reference Sheet'!A3:G100,MATCH(A4&B4,'Reference Sheet'!A$3:A$100&'Reference Sheet'!B$1:B$100),7)} However no matter how I manipulate the formula I get a value of 0, Heres my list of columns: A - The type of steel (Flat bar, etc) ' Worth noting the computer is getting values for this, since its not outputting an error, just a 0 B - The steel's dimensions ' All spelt correctly its an exact match C - Empty D - The constant length of each piece specified on the brochure E - The price per 'length' of material which is specified in D F - Empty G - Price/mm ' The formula =E/D
In the buying sheet, the A column specifies the type of steel and the B column specifies the dimensions, from that point that is all i need to lookup. I am trying to lookup the value of G for a row where the column A and B in the buying sheet match column A and B in the reference sheet.
Update #2: I used the suggestion and got this: =INDEX('Reference Sheet'!G:G,MATCH(A4&B4,'Reference Sheet'!A$3:A$100&'Reference Sheet'!B$1:B$100)) My issue now is that it is only checking for the A column and returning the first result, whereas I want it to check for the dimensions too.
