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.

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.

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.

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##), ""))))

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##), ""))))