simple search function using nested IF and Vlookup statements

Please attach a sample workbook with expected output for better understanding

Try this in C2, sheet1:

Please Login or Register  to view this content.

Confrimed with Ctrl-Shift-Enter

Drag down and across.

Yes, I think it works in 2002.
Adjust the range in the formula with your real data.

Like this then?

-----------

I see that I have not answered your last post here and now it is very old. I assume you have found out that you can move things around quite a bit without ruining the formulas.
However the arrayformula solution that I posted earlier may not work well with 10 000 rows (unless you turn off automatic calculation).

In this workbook is a slightly modified formula. It is more robust and it can handle more rows without slowing down because it is not an array formula. The concatenation is done in separate columns which saves a lot of time.
By the way, don't forget to mark the thread solved if it answers all your questions. (it's under Thread Tools on the top of this thread).

Sorry for late reply.
I'm guessing A20 should be $A20 and B20 should be B$20.

Study the formulas carefully in the workbook I posted in post 13 and you will find that there are carefully placed $ -signs.
The $ -signs will fix a reference vertically or horizontally or both ways. Hope this helps.

Hmm... I realize now that your formula in post #15 is using a single column datatable (sheet1!$P$4:$P$2003). In that case you only need one MATCH formula, not two.
I can't imagine that formula would work very well with $A$20.

It would make much more sence if it looked something like this:

Formula:

Please Login or Register  to view this content.

What is it that you are trying to do anyway?

What I was trying to do is create a search like function where you have:-
sheet 1 with multiple data including name, number and address (in separate columns)
sheet 2 type in a name and number then address will appear on separate lines when the name and number is manually inputted in correct.
The index match only worked for the first 2 i.e. for the first two names and numbers that appeared in sheet 1 but for some reason would not pull up the 3rd or more name, number when typed in sheet 2.
All the data has unique names and number and addresses in.
I'll try the single $ and see if that works. I am essentially matching 2 columns (when manually entered) which brings up data from 4 other columns(which are all in one row).

I had to eventually resort to a simple vlookup where you type in a name and the corresponding address appears and this does the job.

The reason why the first formula does not work is because the MATCH function only returns one number. In the INDEX function you have specified an array with 3 columns ($N$4:$P$27) but that would require a second number, perhaps a second MATCH function, to tell it which column to pick from. Since you don't have that Excel doesn't know what to do, it can't return an entire row to a single cell.

In the second formula you have only a single column in the INDEX function. With a single column (or a single row) it is enough with one number to tell INDEX which cell value to return.


Another part of these formulas is the & operator that is being used in the MATCH function. In order for & to work here you have to enter the formula with Ctrl + Shift + Enter (arrayformula).

A good idea would be to make more simple versions of these formulas and create a mockup sheet with data where you can test them out until you fully understand how they work. Feel free to ask more as well.