Vlookup or match?

[vxs]

Hi All,
I am disappointed I cannot work this out myself

I want to lookup a specific Test String in a pivot table and once matched get the corresponding TOTAL.

Example. I want to find RENT'S grand total in the table below.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]YEAR[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD]APR[/TD]
[TD]MAY[/TD]
[TD]JUN[/TD]
[TD]JULY[/TD]
[TD]AUG[/TD]
[TD]SEPT[/TD]
[TD]OCT[/TD]
[TD]NOV[/TD]
[TD]DEC[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]FUEL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RENT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]£100[/TD]
[/TR]
[TR]
[TD]SHOPPING[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MOBILE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


This CELL is in another worksheet but I can work the referencing out myself I think. Just need the basic formula.

I was trying something along the lines of vlookup("RENT", and then using RIGHT but I cannot get it to work.

Thank you

 

[Misca]

Use INDEX with two MATCHes:

=INDEX(B2:N5,MATCH("RENT",A2:A5,0),MATCH("Total",B1:N1;0))

If your lookup table is on another sheet you're going to have to use the sheet names (or named ranges) in your formulas.

The 0's (=FALSE) in your MATCHes mean you're looking for exact matches.

Also, I'd rather use cell references instead of hard coded words in my formulas.

 

[L. Howard]

With your table in A1:N5 on "a sheet", this formula on the "a sheet" were A2 on sheet2 has "RENT" as the lookup_value...

=VLOOKUP(Sheet2!A2,$A$2:$N$5,14,0)

Howard

 

[vxs]

Use INDEX with two MATCHes:

=INDEX(B2:N5,MATCH("RENT",A2:A5,0),MATCH("Total",B1:N1;0))

If your lookup table is on another sheet you're going to have to use the sheet names (or named ranges) in your formulas.

The 0's (=FALSE) in your MATCHes mean you're looking for exact matches.

Also, I'd rather use cell references instead of hard coded words in my formulas.

Hi,
I am struggling to get either solution to work, thank you for replying both.

In the first solution I get this error:

Thanks

 

[estevaoba]

Try this:

=INDEX(A2:N6,MATCH("RENT",A2:A6,0),MATCH("Total",B2:N2,0))

 

[vxs]

Try this:

=INDEX(A2:N6,MATCH("RENT",A2:A6,0),MATCH("Total",B2:N2,0))

This Returns "0" and not the Total figure I was expecting "£100"

Thanks for the reply

 

[vxs]

Weird if I put numbers in row 4 to see where its getting the value "0" from it now returns "12" column "M" not "N" total figure ?

Am I doing something wrong here?

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl64 {text-align:center;}.xl65 {color:#574123; font-size:13.0pt; font-family:Tahoma; mso-generic-font-family:auto; mso-font-charset:0; text-align:center;}.xl66 {color:#574123; font-size:13.0pt; font-family:Tahoma; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:"\0022£\0022\#\,\#\#0_\)\;\[Red\]\\\(\0022£\0022\#\,\#\#0\\\)"; text-align:center;}--></style><style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl64 {text-align:center;}.xl65 {color:#574123; font-size:13.0pt; font-family:Tahoma; mso-generic-font-family:auto; mso-font-charset:0; text-align:center;}.xl66 {color:#574123; font-size:13.0pt; font-family:Tahoma; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:"\0022£\0022\#\,\#\#0_\)\;\[Red\]\\\(\0022£\0022\#\,\#\#0\\\)"; text-align:center;}--></style>

 

[estevaoba]

I was one column short for the second MATCH.

This will do it:

=INDEX(A2:N6,MATCH("RENT",A2:A6,0),MATCH("Total",A2:N2,0))

 

[Misca]

I try not to use overlapping ranges in my formulas: I'm not expecting values from row 2 nor column A in the INDEX so I'd use B3:N6 as my INDEX range. Also, I don't think I'll be looking for a match for the "YEAR" in my MATCH-ranges so the range for the row part would be A3:A6 and B2:N2 for the column range.

 

[L. Howard]

Maybe something like this. Where there are drop downs in P3 & Q3 (which could be on another sheet if needed). Either VLOOKUP or INDEX/MATCH will work.
The "overlap of ranges" is a non issue and is actually a requirement with the INDEX/MATCH.

I believe the INDEX/MATCH is a better way to go, based on experts comments I've noted.

Howard

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2	YEAR	JAN	FEB	MAR	APR	MAY	JUN	JULY	AUG	SEPT	OCT	NOV	DEC	TOTAL		Expense Item	Mo. or Tot.
3	FUEL	1	2	3	4	5	6	7	8	9	10	11	12	78		MOBILE	TOTAL
4	RENT	13	14	15	16	17	18	19	20	21	22	23	24	222
5	SHOPPING	25	26	27	28	29	30	31	32	33	34	35	36	366
6	MOBILE	37	38	39	40	41	42	43	44	45	46	47	48	510
7
8
9	510
10	510
Sheet3

Cell Formulas
Range	Formula
N3	=SUM(B3:M3)
N4	=SUM(B4:M4)
N5	=SUM(B5:M5)
N6	=SUM(B6:M6)
A9	=VLOOKUP(P3,$A$3:$N$6,MATCH(Q3,2:2,0),0)
A10	=INDEX($A$2:$N$6,MATCH(P3,$A$2:$A$6,0),MATCH(Q3,$A$2:$N$2,0))