Extract text based on two lookup values

[Umesh Chahar]

Hi all ,
i have database which include name, Date , Project , Total Feature , Total metres, and now i want to extract The Project on which somebody has worked . the condition for extracting the project name and the date . for eg i have worked on 11/29/20114 , Ankit so in the result i would expect HSJ as my output . so please suggest some Suitable formula. to make it more clear i have attached excel along with the query.

 

[Asheesh]

Hi Umesh -

Would you like to rephrase your question...as your requirement is not very clear...

 

[Umesh Chahar]

hi Asheesh,
i want to extract a text value which is Project, which from the database which i have created on the workbook shared with you , the value which is coming is based on the two lookupvalues i.e. Name and Date and i want the projectname based on these two criterias. in simple words i want to feetch a value based on two lookup values.

 

[azumi]

Perhaps something like this? Using array formulas, means when you ENTERING the formula you need to press CTRL-SHIF-ENTER button together and copied down.

Azumi

 

[prasaddn]

Hi Umesh,
Assumption: You will not have any different project codes worked by same person on same day.

Looks like we have exact solution to your requirement in the recent article with ultimate vlookup. Here is the link:
http://chandoo.org/wp/2014/10/28/multi-condition-vlookup/

formula for your case:
{=VLOOKUP(B19&C19,CHOOSE({1,2},$A$3:$A$13&$B$3:$B$13,$C$3:$C$13),2,0)}

it explains step by step on how it works as well.

In case my assumption is wrong, then you can always use the formula given by Azumi.

Regards,
Prasad DN

 

[gshayes]

Could someone please explain the use of the curly braces around the formulas? When i try to include them in Excel, the formula doesn't work.

 

[NARAYANK991]

Hi ,

The curly braces around an Excel formula signify that the formula is an array formula , as against a formula which does not have these curly braces , which would be termed a non-array formula.

To give a simple example , suppose you have the numbers as follows :

A1 : 1
A2 : 2
B1 : 3
B2 : 4

You can write a formula such as :

=SUM(A1:A2)

and get the result 3 ; a similar formula with the values in B1 and B2 will give the result 7.

Suppose you wished to multiply each of the values in column A by the corresponding values in column B , and then add the results ? What you want to do would be :

=A1 * B1 + A2 *B2

Try entering the formula :

=SUM(A1:A2 * B1:B2)

Excel displays the error result #VALUE!

Now , enter the formula as an array formula , which means after you type in the formula , instead of pressing the ENTER key , press the 3-key combination of CTRL SHIFT ENTER , often abbreviated to CSE. To do this , press the CTRL key and the SHIFT key , and while these two keys are kept pressed , press the ENTER key. Excel will now display the curly braces around the formula , as shown below :

{=SUM(A1:A2 * B1:B2)}

and the result will be the correct one , 11.

This is because Excel has taken the first set of values A1:A2 as an array , the second set of values B1:B2 as another array , and then performed the multiplication operation of each of the values of the two arrays , which means A1 is multiplied by B1 , and A2 is multiplied by B2 ; thereafter the outer function of SUM is invoked on the two intermediate results of 3 ( 1 * 3 ) and 8 ( 2 * 4 ) , to give the final result of 11.

The above process will remain the same even when thousands of elements are required , as in :

=SUM(A2:A1000 * B2:B1000)

Narayan