Multi-list find feature
Posted by The searcher without a map on September 04, 2001 12:45 PM
How can I find a certain text or number entry in a cell within a huge database of numbers for various cells.
For example, I want to search/find zip code
76429(within a cell) in a huge list composing of other zip codes(both rows AND columns) and with their related states(columns) and if it is located within that state, it will show me.
search number is 76429
State Zips
NY 76400 72400 76800 78001
75320 76429 76324 56801
AL 26540 67542 00221 56701
Since 76429 is within the NY section, the function will tell me it's in "NY."
Let me know if you can assist(I've already tried VLookup and Hlookup on this but because the list is so long with multi-rows and columns, I'm perplexed.
Thanks for the assist!
Posted by Aladin Akyurek on September 04, 2001 4:19 PM
Searcher,
Because your data looks like this:
{"NY",76400,72400,76800,78001;
"",75320,76429,76324,56801;
"AL",26540,67542,221,56701}
I must give you an array formula.
=IF(COUNTIF(B2:E4,76429)=1,INDEX(A1:A4,MAX(IF((LEN(A2:A4)*(ROW(A2:A4)<=SUMPRODUCT((B2:E4=76429)*(ROW(B2:E4))))),ROW(A2:A4)))),"Not Found")
Note. In order to enter an array formula, you need to hit CONTROL+SHIFT+ENTER at the same time, not just ENTER.
You need to adjust the ranges in the above formula to your situation.
You can also put the search number in a cell of its own and use instead that cell's ref in the formula.
If you can change your states column with a macro such that there are no blank cells in the states range, you can use the following ordinary formula:
=INDEX(A1:A4,SUMPRODUCT((B2:E4=76429)*(ROW(B2:E4))))
Aladin
==============
