Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a value Using a lookup table multiple columns and rows
I have a created table with a to k horizontally and 1 to 10 vertically , how
do i write the text to find where i input for example e ,6 and it comes back the value at that point. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a value Using a lookup table multiple columns and rows
=E6
-- Gary''s Student - gsnu200794 "glasspumpkin" wrote: I have a created table with a to k horizontally and 1 to 10 vertically , how do i write the text to find where i input for example e ,6 and it comes back the value at that point. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a value Using a lookup table multiple columns and rows
Thank you for your response I am looking for the formula for the lookup table
A10:M25 so in one cell I can input E and another 6 and the output cell shows the connecting intersection of these cells , I tried this "=HLOOKUP($P$8,$A$10:$M$25,VLOOKUP($P$9,$A$10:$M$2 5,2,FALSE),FALSE) and didn't work just got more confused cells P8 and P9 being the input cells horizontally and vertically "Gary''s Student" wrote: =E6 -- Gary''s Student - gsnu200794 "glasspumpkin" wrote: I have a created table with a to k horizontally and 1 to 10 vertically , how do i write the text to find where i input for example e ,6 and it comes back the value at that point. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a value Using a lookup table multiple columns and rows
On Jul 2, 9:20*am, glasspumpkin
wrote: Thank you for your response I am looking for the formula for the lookup table A10:M25 so in one cell I can input E and another 6 and the output cell shows the connecting intersection of these cells , I tried this "=HLOOKUP($P$8,$A$10:$M$25,VLOOKUP($P$9,$A$10:$M$2 5,2,FALSE),FALSE) and didn't work just got more confused cells P8 and P9 being the input cells horizontally and vertically "Gary''s Student" wrote: =E6 -- Gary''s Student - gsnu200794 "glasspumpkin" wrote: I have a created table with a to k horizontally and 1 to 10 vertically , how do i write the text to find where i input for example e ,6 and it comes back the value at that point. ** [1] & [2] assume there are no duplicates in your number column or alpha lookup ranges, ie more than 1 "A" in the lookup range would result in summing the columns of all the "A" 's ** [1] & [2] are array formulas [1] find reference row: =SUM(IF(##NUMBER COLUMN RANGE## = ##TARGET NUMBER##, ROW(##NUMBER COLUMN RANGE##), "")) [2] find reference column: =SUM(IF(##ALPHA ROW RANGE## = ##TARGET ALPHA##, COLUMN(##ALPHA ROW RANGE##), "")) [3] combine [1] & [2] to get a sheet address: =ADDRESS([1], [2]) [4] get value from address [3] =INDIRECT([3]) Combining all... gets messy, but works ** array formula =INDIRECT(ADDRESS(SUM(IF(##NUMBER COLUMN RANGE## = ##TARGET NUMBER##, ROW(##NUMBER COLUMN RANGE##), "")), SUM(IF(##ALPHA ROW RANGE## = ##TARGET ALPHA##, COLUMN(##ALPHA ROW RANGE##), "")))) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a value Using a lookup table multiple columns and rows
"recrit" wrote: On Jul 2, 9:20 am, glasspumpkin wrote: Thank you for your response I am looking for the formula for the lookup table A10:M25 so in one cell I can input E and another 6 and the output cell shows the connecting intersection of these cells , I tried this "=HLOOKUP($P$8,$A$10:$M$25,VLOOKUP($P$9,$A$10:$M$2 5,2,FALSE),FALSE) and didn't work just got more confused cells P8 and P9 being the input cells horizontally and vertically "Gary''s Student" wrote: =E6 -- Gary''s Student - gsnu200794 "glasspumpkin" wrote: I have a created table with a to k horizontally and 1 to 10 vertically , how do i write the text to find where i input for example e ,6 and it comes back the value at that point. ** [1] & [2] assume there are no duplicates in your number column or alpha lookup ranges, ie more than 1 "A" in the lookup range would result in summing the columns of all the "A" 's ** [1] & [2] are array formulas [1] find reference row: =SUM(IF(##NUMBER COLUMN RANGE## = ##TARGET NUMBER##, ROW(##NUMBER COLUMN RANGE##), "")) [2] find reference column: =SUM(IF(##ALPHA ROW RANGE## = ##TARGET ALPHA##, COLUMN(##ALPHA ROW RANGE##), "")) [3] combine [1] & [2] to get a sheet address: =ADDRESS([1], [2]) [4] get value from address [3] =INDIRECT([3]) Combining all... gets messy, but works ** array formula =INDIRECT(ADDRESS(SUM(IF(##NUMBER COLUMN RANGE## = ##TARGET NUMBER##, ROW(##NUMBER COLUMN RANGE##), "")), SUM(IF(##ALPHA ROW RANGE## = ##TARGET ALPHA##, COLUMN(##ALPHA ROW RANGE##), "")))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Duplicates Rows Based Own Multiple Columns | Excel Discussion (Misc queries) | |||
Lookup Multiple Rows in Table | Excel Discussion (Misc queries) | |||
Make pivot table with multiple "data" fields in columns not rows? | Excel Discussion (Misc queries) | |||
find number in multiple columns and rows | Excel Worksheet Functions | |||
how to interchange rows to columns & columns to rows in a table | Excel Discussion (Misc queries) |