vlookup formula with two criteria that must be met to return a value

**MBROOKS** · 10-12-2012, 03:47 PM

This is my formula: IF(ISERROR(VLOOKUP(A7,[FTEBYWORKEDDEPT.xlsm]FTE!$E$1:$K$3500,6,FALSE)),"",VLOOKUP(A7,[FTEBYWORKEDDEPT.xlsm]FTE!$E$1:$K$3500,6,FALSE))

I want to make this formula search two fields (A7 & C31). Example: A7 = "SMITH, JOHN" which searches the spreadsheet "FTEBYWORKEDEPT.xlsm". C31 = "001".

I basically want my formula to search for two criteria (A7 & C31) before it returns a value. Both criteria must be met before a value is returned.

I need some help....hope I didn't confuse anyone too much!

Confused!

**MBROOKS** · 10-12-2012, 04:23 PM

Can this even be done?

**MBROOKS** · 10-12-2012, 04:57 PM

In theory (in my mind) the formula should look like this:

IF(ISERROR(VLOOKUP(A7&C31,[FTEBYWORKEDDEPT.xlsm]FTE!$E$1:$K$3500,6,FALSE)),"",VLOOKUP(A7&C31,[FTEBYWORKEDDEPT.xlsm]FTE!$E$1:$K$3500,6,FALSE))

This of course doesn't work though....this is driving my NUTS!

**FDibbins** · 10-12-2012, 05:10 PM

have you tried to combine the 2 values in your lookup range, and then doing the vlookup(A7&C31

upload a sample workbook and i will walk you thru it

**gjcase** · 10-12-2012, 05:37 PM

Can't you just use AND statements?

IF(AND(ISERROR(VLOOKUP(A7,[FTEBYWORKEDDEPT.xlsm]FTE!$E$1:$K$3500,6,FALSE)),ISERROR(VLOOKUP(B7,[FTEBYWORKEDDEPT.xlsm]FTE!$E$1:$K$3500,6,FALSE)),"",VLOOKUP(A7,[FTEBYWORKEDDEPT.xlsm]FTE!$E$1:$K$3500,6,FALSE))

---GJCase

**MBROOKS** · 10-12-2012, 06:06 PM

Where in the formula would I enter in C31?

**FDibbins** · 10-12-2012, 07:04 PM

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.