lookup data in the database of Excel Tables WITHOUT AN ARRAY FORMULA

I have a database in excel that I converted to “Excel Tables” to eliminate the many dynamic named ranges I was using.

My next goal is, FROM A DIFFERENT WORKBOOK to lookup data in the database of Excel Tables WITHOUT AN ARRAY FORMULA { Ctrl-Shft-Enter }.
(I realize INDEX is technically an array but not for the purposes of this conversation.)

I found a solution but it only works with exact matches and I need it to match on >=.
(BTW, There are four columns involved and it is simply not possible to sort all data ascending or descending in every column of interest at once.)

See the workbook attached. (In the sample attached workbook the table resides in the same workbook as the formula.)

*********
For those not willing to open the workbook I will attempt to describe:
Table1 Column Headings:
T....W...L..G..C
187 4 10 36 1
.187 6 20 36 2
.250 4 10 36 3
.250 6 20 36 4

This works for an exact match:
=INDEX(Table1[[#All],[C]], MATCH(J1&J2&J3&J4, INDEX(Table1[[#All],[T]]&Table1[[#All],[W]]&Table1[[#All],[L]]&Table1[G],),0))
Search Criteria of .187, 6, 20, 36 correctly returns 2.00 from column heading “C”


How to obtain the same result using search criteria of .187, 5, 20, 36
“5” should match the row containing 6 in “W” as 6 >= 5 and return 2.00 from column heading “C”

I tried adding >=J2 with no luck.
= INDEX(Table1[[#All],[C]], MATCH(J1& ">=J2” &J3&J4, INDEX(Table1[[#All],[T]]&Table1[[#All],[W]]&Table1[[#All],[L]]&Table1[G],),0))

Let me know if this works for you.

Please Login or Register  to view this content.

No it did not.

For some reason I can't upload the workbook. Maybe it's my employer. I've not had problems in the past uploading from elsewhere.

bump bump bump

It's difficult for me to tell what the issue is without more information. Maybe try uploading a sample file again when you're not at work and sending me another reply. Sorry I'm not more help with the current information.

see attached workbook

I can't seem to come up with a solution that doesn't use array formulas. If you decide that you can accept an array formula solution, you can use the formula below to produce your desired result.

Please Login or Register  to view this content.

Best of luck!

Bump ....still looking for a non-array solution

Try this in L1

=LOOKUP(2,1/((Table1[T]=N1)*(Table1[W]>=N2)*(Table1[L]=N3)*(Table1[G]=N4)),Table1[C])

FDibbins
The main reason for non-array is I'm the only one in my company that knows what an array formula is and training is a futile effort. The users don't report to me.
Additionally, moving from Dynamic named ranges to Excel Tables means the future database admin don't have to insure all cells in a column contain data.
I have a cost calculator that runs without any user input and is chock full of array formulas and often takes longer than you care to wait to calculate.
But the main reason is the new cost calculator I am working on there will be a lot of user interface. I have the page protected just to keep people from accidently overwriting something but they need to be able to unprotect and carefully make changes from time to time. I can't rely on the users to remember to use Ctrl-Shft-Enter. I have comments on all the field headings "ARRAY FORMUAL {} Ctrl-Shft-Enter" but this is a disaster waiting to happen. I can't create a zillion userforms for their custom data input as this needs to be a quick process.


Jonmo1
You formula works exactly as I requested on my sample, however I was not complete in my request.
I also need >=N3

The problem is the same data in T W L & G repeat and they CANNOT be sorted such that all columns are ascending, which is why I initially ignored the LOOKUP option.

my current array formula with named ranges for reference is:
INDEX(RngDbC,MATCH(1,(RngDbT = N1)*(RngDbW >= N2)*(RngDbL >= N3)*(RngDbG = N4),0),1)

Are you able to use macros?
You could set up an event code that auto array-enter a formula if a user makes changes to it.


Anyway, you should be able to change the =N3 to >=N3 in the formula I posted.

When you put in .250, 3, 9 & 36 the answer I want is 3 or the row containing .250, 4, 10 & 36
>=N3 returns 4 which does meet the criteria but so does 3 and I want 3

I want the minimum >= not just anything >=


Yes, worksheetchange code is an option but I would like to not have sheet change code running unless necessary

=LOOKUP(2,1/((Table1[T]=N1)*(Table1[W]>=N2)*(Table1[L]=N3)*(Table1[G]=N4)),Table1[C])

So that works, but it returns the last one that meets the criteria (MAX)
And you want the FIRST one that meets the criteria (MIN)

=LOOKUP(2,1/((Table1[T]=N1)*(Table1[W]>=N2)*(Table1[L]=N3)*(Table1[G]=N4)),Table1[C])

Try instead

=INDEX(Table1[C],MATCH(1,INDEX((Table1[T]=N1)*(Table1[W]>=N2)*(Table1[L]>=N3)*(Table1[G]=N4),0),0))

SUCCESS!

Thank you! Would you believe I've used the Index buried in the Match to eliminate the array in this same workbook and it's just so huge I couldn't remember it to find it !

You're welcome