Combo H ad V lookup

  • Thread starter Thread starter Jas
  • Start date Start date
J

Jas

Constraint with Vlookup is you need to know the column
and Hlookup is the row. Is there a combo or different
function to use to find a match in a table that will find
the answer if the order of rows and columns change
frequently. Example you have an output from one
spreadsheet that is a large table with a row header say
with month/year and column a = item codes and the table
is populated with sales qunatities per month etc. Another
sheet then needs to find the quantity of a particular
item for a particular month?
 
Example is as follows:
a b c d e f
1 Jan Feb Mar Apr May
2 apples 10 25 37 53 60
3 balls 2 19 5 7 14
4 caps 57 27 3 5 8
5 darts 0 4 8 27 18

need a formula in another file to find Qty for say Caps
for Mar. I can do using V or H lookups assumping the
table layout stays in the same order. But how do write
the formula so that it will still get a result if the
order of both the columns (b-f) change and/or rows 2-5
change. Assume headers remain in the row and name the
same column?
 
=INDEX($B$2:$F$5,MATCH("caps",$A$2:$A$5),MATCH("Mar",$B$1:$F$1)

or

=VLOOKUP("caps",$B$2:$F$5,MATCH("Mar",$B$1:$F$1,0),0)
 
Back
Top