Using VLookup with multiple criteria

April 9, 2011 at 07:24:26
Specs: Windows 7
I am using Excel 2007 to create a form. What I would like to happen is that based on the value entered in two cells, that a search is done on a second worksheet to find the related value and have that automatically populated in another cell on the first work sheet.

Better explanation:

I am trying to get the form (sheet1) to automatically populate the appropriate value based on what paper form was used (cell E19) and how many hits were made (cell H19). Depending on what paper form was used and how many hits were made, a specific score or points are assigned. This score should populate in cell J19 are on sheet2.

Currently, the person is supposed to enter the form name. Would it be more advantageous if I used a list to eliminate human error and misspelling? This question is of course secondary to what formula I need to enter in cell J19 to get the results that I am looking for. Please let me know if you need any additional info to help me.

Any help is SO appreciated and welcomed!!!

Thanks,

April


See More: Using VLookup with multiple criteria

Report •

#1
April 10, 2011 at 09:50:16
From your description, with no example of your data, I think this is what your looking for:

If your data looks like:

Sheet number 4 of workbook:

         A           B                C
 1) Critera 1	Critera 2	Return Value
 2)		
 3) ALPHA	ONE	            20
 4) BETA	TWO	            21
 5) GAMMA	THREE	            22
 6) DELTA	FOUR	            23
 7) EPSILON	FIVE	            24
 8) ZETA	SIX	            25
 9) ETA	        SEVEN	            26
10) THETA	EIGHT	            27
11) IOTA	NINE	            28
12) KAPPA	TEN	            29

On Sheet number 1 of workbook,
in cell A13 enter first search critera: Gamma

On Sheet number 1 of workbook,
in cell B13 enter second search critera: Three

On Sheet number 1 of workbook,
in cell C13 enter this formula:

=SUMPRODUCT((Sheet4!$A$3:$A$12=A13)*(Sheet4!$B$3:$B$12=B13)*(Sheet4!C3:C12))

You should get the return value of 22

MIKE

http://www.skeptic.com/


Report •

#2
April 11, 2011 at 08:59:19
Mike:

Please send me your email address so that I can send you the form and a more detailed example of my problem.

Thanks,

April


Report •

#3
April 11, 2011 at 11:57:33
Please send me your email address so that I can send you the form and a more detailed example of my problem.

The forums are a place for everyone to learn, so you sending me the form kinda defeats the purpose of the forums, does it not?

Let's see if we can solve your problem on line first, OK?

What I would like to happen is that based on the value entered in two cells,
that a search is done on a second worksheet
to find the related value and have that automatically populated in another cell on the first work sheet.

That's what I gave you,
just modify the cell numbers on Sheet 1 to:

On Sheet number 1 of workbook, in cell E19 enter first search criteria: Gamma

On Sheet number 1 of workbook, in cell H19 enter second search criteria: Three

On Sheet number 1 of workbook, in cell J19 enter the formula:


Here's a breakdown of the formula:

Sheet2!$X$X:$X$X=E19 is the range of cells on sheet 2 you want cell E19 to check, no blank cells allowed.

Sheet2!$X$X:$X$X=H19 is the range of cells on sheet 2 you want cell H19 to check, no blank cells allowed.

Sheet2!XX:XX is the range of cells on sheet 2 you want to pick from when the above two match, no blank cells allowed.

=SUMPRODUCT((Sheet2!$X$X:$X$X=E19)*(Sheet2!$X$X:$X$X=H19)*(Sheet2!XX:XX))


If you post a small sample of your second worksheet, then modifying the formula
should not be very difficult.

Unless there is more to this problem that you haven't revealed.

Read this How-To before you try to post your data:
http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
April 11, 2011 at 15:59:31
On Sheet1, in cell G11 I created a list that consists of the values E5 and E6. Depending on which one of these is chosen will determine which list to do a look up on in Sheet3 which contains physical fitness scores. E5 scores vary greatly from those for E6. Depending on which "list" is used, E5 or E6, the value that is calculated in cell H16 (which comes from adding cells E15+G15+I15) will equal a value on Sheet3. For example, if an E5 scores 215 then J16 should populate with a value of 75. If it is an E6, then J16 should be 31. This is based on the list on Sheet3 which has Cell A1:B1 merged and the text E5. Cell A2 is Score, and cell B2 is Points. Cells A3/B3 - A123/B123 has the allowed score/point computation. The same thing occours in cells D1:E1 (merged with E6 as the text) and D2/E2 being a replica of A2/B2 with the exception that the points in column E vary from the points in column B.

My next issue is still based on which value is selected for cell G11. In cell E19, a list consisting of various forms is available. Depending on which form, and how many points were made, then cell J19 should be populated based on the information found on Sheet2. For example, if it is E5, and form A was used and 32 points were made, then J19 should populate with 100. If it were an E6 and form D was used, and 36 points were made then J19 should be 82.

I suggested the email so that I could send you the exact form. I hope that my explanation here is better than my first (and second) attempt for help.

Again, your help is definitely appreciated!

April


Report •

#5
April 11, 2011 at 17:24:23
On Sheet1, in cell G11 I created a list that consists of the values E5 and E6.
Depending on which one of these is chosen will determine which list to do a look up on in Sheet3

Depending on which "list" is used, E5 or E6, the value that is calculated in cell H16 will equal a value on Sheet3.

So you will need some type of =IF() statement to first determine which list to use, correct?

ADDED:

This is based on the list on Sheet3 which has Cell A1:B1 merged and the text E5.

Things get problematic with merged cells.


MIKE

http://www.skeptic.com/


Report •

Ask Question