Lookup formula for both row and column
 

I am trying to do a lookup on both row and column. My spreadsheet has
targets per cost center (Rows) by Months (Columns) starting in Column D. The
"Row" lookup is working but how do I add the second portion. See ???? below

This is the formula I have so far:

=VLOOKUP($A3,TLTargets!$A$46:$Q$86,????,FALSE)

Lookup formula for both row and column
 

I can see that you realise that you need to lookup the column number. You
could use the MATCH function to do this. For example, if your column
headings are in row 45, use:-

MATCH("Apr",$A$45:$Q$45,0)

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"gcw" wrote:

I am trying to do a lookup on both row and column. My spreadsheet has
targets per cost center (Rows) by Months (Columns) starting in Column D. The
"Row" lookup is working but how do I add the second portion. See ???? below

This is the formula I have so far:

=VLOOKUP($A3,TLTargets!$A$46:$Q$86,????,FALSE)

Lookup formula for both row and column
 

So this "match" formula should go in the middle of the formula below where
the ???? are?

"Alan Moseley" wrote:

I can see that you realise that you need to lookup the column number. You
could use the MATCH function to do this. For example, if your column
headings are in row 45, use:-

MATCH("Apr",$A$45:$Q$45,0)

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"gcw" wrote:

I am trying to do a lookup on both row and column. My spreadsheet has
targets per cost center (Rows) by Months (Columns) starting in Column D. The
"Row" lookup is working but how do I add the second portion. See ???? below

This is the formula I have so far:

=VLOOKUP($A3,TLTargets!$A$46:$Q$86,????,FALSE)

Lookup formula for both row and column
 

Hi,

=VLOOKUP($A3,TLTargets!$A$46:$Q$86,MATCH(Month,Mon ths,0),FALSE)

Where Month is the month you want to find and Months is the range (row where
the months are listed)

A more standard way to do this would be to use Match with INDEX or OFFSET:

=INDEX(TLTargets!$A$46:$Q$86,MATCH(A3,TLTargets!$A $46:$A$86,0),MATCH(Month,Months,0))

Cheers,
Shane Devenshire

"gcw" wrote:

I am trying to do a lookup on both row and column. My spreadsheet has
targets per cost center (Rows) by Months (Columns) starting in Column D. The
"Row" lookup is working but how do I add the second portion. See ???? below

This is the formula I have so far:

=VLOOKUP($A3,TLTargets!$A$46:$Q$86,????,FALSE)

Lookup formula for both row and column
 

That's right, replace the ????s with the MATCH formula. You may have to
adjust the column number that gets returned from the match.
--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"gcw" wrote:

So this "match" formula should go in the middle of the formula below where
the ???? are?

"Alan Moseley" wrote:

I can see that you realise that you need to lookup the column number. You
could use the MATCH function to do this. For example, if your column
headings are in row 45, use:-

MATCH("Apr",$A$45:$Q$45,0)

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"gcw" wrote:

I am trying to do a lookup on both row and column. My spreadsheet has
targets per cost center (Rows) by Months (Columns) starting in Column D. The
"Row" lookup is working but how do I add the second portion. See ???? below

This is the formula I have so far:

=VLOOKUP($A3,TLTargets!$A$46:$Q$86,????,FALSE)

Lookup formula for both row and column
 

Use this method

http://www.contextures.com/xlFunctio...ml#IndexMatch2

--


Regards,


Peo Sjoblom

"gcw" wrote in message
...
So this "match" formula should go in the middle of the formula below where
the ???? are?

"Alan Moseley" wrote:

I can see that you realise that you need to lookup the column number.
You
could use the MATCH function to do this. For example, if your column
headings are in row 45, use:-

MATCH("Apr",$A$45:$Q$45,0)

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"gcw" wrote:

I am trying to do a lookup on both row and column. My spreadsheet has
targets per cost center (Rows) by Months (Columns) starting in Column
D. The
"Row" lookup is working but how do I add the second portion. See ????
below

This is the formula I have so far:

=VLOOKUP($A3,TLTargets!$A$46:$Q$86,????,FALSE)

Lookup formula for both row and column
 

Thank you these examples are great!

"Peo Sjoblom" wrote:

Use this method

http://www.contextures.com/xlFunctio...ml#IndexMatch2

--


Regards,


Peo Sjoblom

"gcw" wrote in message
...
So this "match" formula should go in the middle of the formula below where
the ???? are?

"Alan Moseley" wrote:

I can see that you realise that you need to lookup the column number.
You
could use the MATCH function to do this. For example, if your column
headings are in row 45, use:-

MATCH("Apr",$A$45:$Q$45,0)

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"gcw" wrote:

I am trying to do a lookup on both row and column. My spreadsheet has
targets per cost center (Rows) by Months (Columns) starting in Column
D. The
"Row" lookup is working but how do I add the second portion. See ????
below

This is the formula I have so far:

=VLOOKUP($A3,TLTargets!$A$46:$Q$86,????,FALSE)