Search function

[scareypirate]

Hi peeps. I really need some help here and know this is the place to ask.

What I need is as follows:

I have a grid of car registration numbers. I need the ability to have a search box and enter a car registration number. If a match is found I need it to give me the location of that reg number.

Example:

Search 'WJ05ASP' would return 'A4' as WJ05ASP is located in the column A Row 4.

I have searched here but can't seem to find anyone asking this.

Thanks anyone that can help me here. And if you require a excel attach example to play with then I will provide one.

All the best

 

[T. Valko]

Hi peeps. I really need some help here and know this is the place to ask.

What I need is as follows:

I have a grid of car registration numbers. I need the ability to have a search box and enter a car registration number. If a match is found I need it to give me the location of that reg number.

Example:

Search 'WJ05ASP' would return 'A4' as WJ05ASP is located in the column A Row 4.

I have searched here but can't seem to find anyone asking this.

Thanks anyone that can help me here. And if you require a excel attach example to play with then I will provide one.

All the best

Can you give us the specific range of your grid?

I assume that within this grid a registration number will only be entered once (or not at all).

 

[barry houdini]

Hello scareypirate, welcome to MrExcel,

Assuming the grid is A1:J10 and lookup value is in Z2 you could try this "array formula"

=ADDRESS(MIN(IF(A1:J10=Z2,ROW(A1:J10))),MIN(IF(A1:J10=Z2,COLUMN(A1:J10))),4)

Assumes that Z2 only occurs once in A1:J10

 

[scareypirate]

Yes the reg will appear just once only.

And I need to add that I am really green with excel so I tried the '=ADDRESS(MIN(IF(A1:J10=Z2,ROW(A1:J10))),MIN(IF(A1:J10=Z2,COLUMN(A1:J10))),4)'

But don't know where to enter the search. The grid is A to T - 1 - 13

Sorry I really am a noob at excel but have offered to work on this as I like excel and the amount of things that can be accomplished is astonishing.

Thanks so far I will work out the formula you gave I'm sure

 

[barry houdini]

OK try this

=ADDRESS(MIN(IF(A1:T13=Z2,ROW(A1:T13))),MIN(IF(A1:T13=Z2,COLUMN(A1:T13))),4)

where you have the value to search for in Z2 (change that to any cell you want).

That's an "array formula". Paste in a cell then press F2 key to select formula and hold down CTRL and SHIFT keys and press ENTER. Curly braces like { and } should appear automatically around the formula

 

[scareypirate]

OK try this

=ADDRESS(MIN(IF(A1:T13=Z2,ROW(A1:T13))),MIN(IF(A1:T13=Z2,COLUMN(A1:T13))),4)

where you have the value to search for in Z2 (change that to any cell you want).

That's an "array formula". Paste in a cell then press F2 key to select formula and hold down CTRL and SHIFT keys and press ENTER. Curly braces like { and } should appear automatically around the formula

I got the curly brackets as you said, thanks. If I type the reg in Z2 it wipes out the formula I know I'm missing something and making myself sound stupid but is it Z2 I need to type the reg and get it to display its coords?

Sorry I know I'm going to p' you off soon with my idiocy

 

[barry houdini]

OK, sorry, no - the formula needs to go in one cell, e.g. Y2 and then you type the reg into the cell beside it, Z2.......does that make sense?

 

[T. Valko]

Yes the reg will appear just once only.

And I need to add that I am really green with excel so I tried the '=ADDRESS(MIN(IF(A1:J10=Z2,ROW(A1:J10))),MIN(IF(A1:J10=Z2,COLUMN(A1:J10))),4)'

But don't know where to enter the search. The grid is A to T - 1 - 13

Sorry I really am a noob at excel but have offered to work on this as I like excel and the amount of things that can be accomplished is astonishing.

Thanks so far I will work out the formula you gave I'm sure

Ok, in that formula Z2 is the cell you enter the registration number that's to be searched for. You can use any cell you want just so that it's outside of the grid.

Just change the range references to suit your grid.

Array entered**:

=ADDRESS(MIN(IF(A1:T13=Z2,ROW(A1:T13))),MIN(IF(A1:T13=Z2,COLUMN(A1:T13))),4)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[scareypirate]

Thank you both for your kind help. I now understand how to impliment but each search returns a '#VALUE!'

I have uploaded a test piece, if you have the time I would appreciate your input. Sorry can't find how to upload so I have a link to the file:

http://www.mypersonalpage.talktalk.net/EXCEL/test1.xls

Thanks again for your time thus far

 

[T. Valko]

Thank you both for your kind help. I now understand how to impliment but each search returns a '#VALUE!'

I have uploaded a test piece, if you have the time I would appreciate your input. Sorry can't find how to upload so I have a link to the file:

http://www.mypersonalpage.talktalk.net/EXCEL/test1.xls

Thanks again for your time thus far

Try this...

The formula is entered in a merged cell.

I recommend not using merged cells. You can get the same effect by doing:

Select the cell with the formula
Right click>Format Cells
Alignment tab
Uncheck: Merge Cells
In the Horizontal drop down select: Center across selection
OK out

With the formula cell still selected...
Hit function key F2
Array enter the formula

Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo.