Solved: Two input variables at vlookup

[danlu]

Hi

I am looking for some solution where you can input two variables to be found at some other sheet and if found one or more values to the right of these look up values should be retrieved.

For ex if I have the names:
Mark Spencer 79000
Mark Spencer 79100
Mark Spencer 79200

and on another sheet (called sheet2) I have:

Mark Spencer 79000 cash
Mark Spencer 79100 settling
Mark Spencer 79200 allowance
....
and a lot more of values...so in total 200 rows have data in sheet 2 with three columns as shown above (ie 1st column: name, 2nd column:departmentid, 3rd column of holding)

and I would like to use I function that would look something like this:
=function_name('Mark Spencer';'79000';lookuparea;return value from the column to the right of my two lookup values on the same row as these look up values)

so for Mark Spencer 79000 it will return value: cash.

If possible I would also like to be able to determine from how many cells to the right of the look up values that should be retrieved. For ex. if there would be more than 3 columns on sheet2 I would like to be able to tell the function to retrieve both the first value AND the second value to the right of the lookup values (ie in this case the values in column 3 and 4 on the row where the function gets a match).

All ideas and possible solutions are highly appreciated.

[rory]

If there is only one match for the criteria, you can use something like this:
=INDEX(Sheet2!$C$1:$C$200,SUMPRODUCT((Sheet2!$A$1:$A$200=A2)*(Sheet2!$B$1:$ B$200=B2)*ROW(Sheet2!$B$1:$B$200))

[danlu]

Hi,

First a had a bit of a trouble to make it work but after some trying it worked (don't know if I really modified anything...)

INDEX(Sheet2!$C$1:$C$200;SUMPRODUCT((Sheet2!$A$1:$A$200=A1)*(Sheet2!$B$1:$B $200=B1)*ROW(Sheet2!$B$1:$B$200)))

So thanks a lot for your help.
Though I have encountered a need to be able to have up to four input parameters in a lookup function ( that is, four input parameters have to match values in the search area in order to allow the function to retrieve a value from a column to the right of the four columns where the function has found a match for the four input parameters).

All ideas or tips are welcome!

[Bob Phillips]

An array formula, but simpler

=INDEX(Sheet2!$C$1:$C$200,MATCH(1,(Sheet2!$A$1:$A$200=A2)*(Sheet2!$B$1:$B$2 00=B2),0))

[danlu]

To have four input variables (for ex i column A,B,C and D) and to use these to match it against the datamaterial on sheet2 and retrieve a value from column E on Sheet2 (when a match is found,that is column A,B,C and D match) should it be set up something like this:
INDEX(Sheet2!$E$1:$E$200,MATCH(1,(Sheet2!$A$1:$A$200=A2)*(Sheet2!$B$1:$B$20 0=B2)*(Sheet2!$C$1:$C$200=C2)*(Sheet2!$D$1:$D$200=D2),0))

??

I haven't managed to get it working. Maybe this isn't the proper way to set it up?
Can this be done without an array formula or is an array formula needed?
Rory included a ROW-function in his solution, isn't that needed here in order to be able to copy the formula through all rows (ie all rows down to row 200) ?

[Bob Phillips]

I just ran that formula and it works fine for me.

You don'r need the Row, The Match gets the row index, which is why it is simpler.

[danlu]

Ok,
I will have to try it out again then. How did you find the view of the function with four input parameters, did I do the right thing when I extended your function in the way I did? (that is to add the following : *(Sheet2!$C$1:$C$200=C2)*(Sheet2!$D$1:$D$200=D2)

to your function).

[Bob Phillips]

Yes, I just picked up what you put in your post, prefixed it with =, and after Excel told me there was an error, an embedded space, and let Excel correct it, it worked fine.

[danlu]

I missed out the array part. When adding SHIFT+CTRL and ENTER it worked. Though not the first time. For experimenting I wrote down a small sample of my data material (to have a more managable amount of data) which I ran the array formula against and then it worked. Maybe this sort of formulas is more sensitive to data formats than the VLOOKUP function ?

[danlu]

Another question, could this function be extended to retrieve more than one value, that is, values from the same row but in columns to the right of the E column in sheet2? For ex when a match is found then retrieve the values in columns E,F,G,H,I,J och K from sheet2.

[Bob Phillips]

Yes,

=INDEX(Sheet2!E$1:E$200,MATCH(1,(Sheet2!$A$1:$A$200=A2)*(Sheet2!$B$1:$B$2 00=B2),0))

=INDEX(Sheet2!F$1:F$200,MATCH(1,(Sheet2!$A$1:$A$200=A2)*(Sheet2!$B$1:$B$2 00=B2),0))

etc.

maybe best to put the MATCH in a separate cell and referenc that.

[danlu]

Here I guess the fomrula will have to be written in all cells who are to receieve a value from sheet2 via the formula ? Is there some way (maybe a function is not the proper way but instead use a sub procedure??) to write a formula only once and thererafter it will populate the cells with values that are specified in the formula, that is, if you want to retrive values from both column E,F,G,H,I,J on sheet2 when the formula get a match then these values should be retrieved and set on the same row as the input parameters (on the sheet where the formula is) but in the columns to the right of these input parameters?

[Bob Phillips]

No a function can only return a value to the cell it occupies, it cannot write to other cells. So you would need to put the function in each cell.

But the way I crafted the formula, wit absolute lookup tables, but a relative column INDEX means that you can enter it in one column, copy across and it auto-updates.

[danlu]

Hi,
I guess you mean leave out the dollar sign at the first column reference and include dollar signs around each input parameter's column reference, like this:
=INDEX(Sheet2!E$1:E$200,MATCH(1,(Sheet2!$A$1:$A$200=A2)*(Sheet2!$B$1:$B$2 00=B2),0))

It worked for me, so I am very greatful to you. You have really made my life easier! Thanks.