Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Double column lookup formula Help | Excel Worksheet Functions | |||
LookUp Function with Two Column Search Returning One Column Value | Excel Worksheet Functions | |||
LOOKUP FORMULA INTERSECTION OF A COLUMN AND (TWO) VALUES IN A ROW | Excel Worksheet Functions | |||
Formula to lookup Multiple Column Text and then Count Result | Excel Worksheet Functions | |||
Calculating totals in a column based on a lookup in another column | Excel Worksheet Functions |