Formula for table array

Help would be much appreciated on a problem I have.

I will try my best to explain.

In a spreadsheet I have a large table in one tab with a row of numbers, and column of numbers with an array of numbers under each.

Closes example I could find on google is

http://www.csus.edu/indiv/j/jensena/.../eld_npe_t.jpg

except instead of words for months I have numbers.

In another tab I have input cell where in the above picture for example I would type the month in one cell, and the year in the other.

In another row I would then need the answer displayed that relates to that month and year.

The other issue I have is that when imputing two values to search for, they wont be the exact values in the look up table.

I.e

The look up table lists in the first column, 1,1.2,1.4,1.6 etc
and the first row lists 2,4,6,8,10

and in-between would be a vast amount of numbers

I would enter 1.5 and 5 to look up
which I would want to return the next highest value, relating to 1.6 and 6

so I need to but in a more than part to a double look up equation??

Thank you for your help and time.

Welcome to the forum.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

I would say so yes... but to further assist...

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

When you post a reply use the paperclip icon above the message box to manage attachments (if you can't see it click "Go Advanced" below the message box)

Based on your sample file and narrative in first post I think perhaps:

Please Login or Register  to view this content.

DonkeyOte,

What does this part of the formula accomplish?

+ISNA(MATCH($D6,'Lookup Table'!$A$4:$A$14,0))

Edit: never mind, I understand it adds "1" to advance to the next row/column if there is not exact match found.

Consider: A1:A3

1
10
15

Criteria: 12

Requirement: return match position of first number >= criteria

MATCH(12,A1:A3) --> returns 2 given 10 is the last value <= criteria

given we want to return 3 as 15 is the real value we want we can just test to see if 12 exists in the range, if it does the answer stands, if not add 1 to result...

ISNA(MATCH(12,A1:A3,0))

if the above returns TRUE we know 12 does not exist in A1:A3 so we add 1 to result... if criteria were 10 the ISNA would return FALSE thus result would remain as 2.

Re: formula

One obvious change would be to change

=IF(COUNT($H15:$P15)=2,

to

=IF(COUNT($H$15,$P15)=2,

If altering the above does not resolve the circular reference we'd first need to know in which cell the formula resides etc...

Just out of interest, what does the =2 bit mean?

Simply checks to make sure both cells contain a numerical value before attempting to run the Matches, in reality you could extend to make the test more robust (to ensure numerical values are within min range) but as is may prove sufficient.