V-lookup for two criteria

[Collock]

I know I have read a tutorial on how to due this somewhere I just don't remember were. Could someone please point me to how I can due row and column look-up vlookup (might need to use sumproduct but can’t remember) . Aka I have multiple lines with different types of equipment. I want the equipment to go to the right line (row), and equipment type (column). Please note I can due this in a pivot table but there is to much data to not be able to due relative references plus many of the items will be blank for a while.


That is unless someone has figured out how to use the pivot formula with iserror to reference cells with the criteria in a pivot table that allows relative references. Can’t seem to get the sytex right so


=GETPIVOTDATA("Extended Price",Sheet2!$A$3,"Area",$A3,"Area2",d$2). A3 and d2 have the specific information to look up in the pivot table. Can’t get the sytex right for one formual let alone one that lets me drop and drag.


However, I am sure a V-look-up/sumproduct is more simple .

 

[Hui]

Pretty much everything you'll ever need to know about Vlookup can be found at: http://chandoo.org/wp/tag/vlookup-week/

 

[JesseH77]

Collock, an effective way of doing this is to use offset and match


=INDEX(A:Z,MATCH(A5,6:6,0),MATCH(A6,B:B,0))


I'm using made up ranges but the gist is replace A:Z with the range of your table, I like to use entire columns, makes it easier to read.


The first match finds the row from your headings, in your case specific equipment. You would change 6:6 to be the row with your headings.


The second match does the same but for your columns.


Jesse