Lookup first and last name and return a value from that row

Hello,

assuming your sheets have headers in row 1 and the formula starts in row 2, use this formula in E2 and copy down:

=IFERROR(INDEX(Sheet1!$E$1:$E$10,MATCH(Sheet2!B2&Sheet2!C2,INDEX(Sheet1!$A$1:$A$10&Sheet1!$B$1:$B$10,0),0)),"not found")

This works well with smaller data sets. For large tables it can get a bit slow. Always use fixed references, not whole columns.

If this gets too slow, a helper column as suggested by Wyman W is advisable. 

You may also try in cell E2 of sheet2 and drag down as per this screen shot,

=IFERROR(LOOKUP(2,1/((Sheet1!$A$1:$A$4=B2)*(Sheet1!$B$1:$B$4=C2)),Sheet1!$E$1:$E$4),"Not Found")

This is what I'm currently doing, but it causes some minor inconveniences. Namely inserting the column, keeping track of the sort order between workbooks, and #N/A values (there are only around 70 matches per 1000 rows).

I've been playing around with index and match and =INDEX($A$1:$E$4,MATCH(1,($A$1:$A$4=I6)*($B$1:$B$4=J6),0),5)   removes some of these problems but I still get those pesky #N/A results.

I'm also copying my two columns of data from one workbook 2 to workbook 1 to get my results and then pasting my results back into workbook 2.

Again minor inconveniences but life changing if I can find a way to end them.  

Thanks

Hi, I am assuming you want to pull the information from column C workbook1

Assumed that in workbook1 the information is in a sheet called sheet1

This is an array formula has to be entered pressing CTRL+Shift+Enter

=INDEX([workbook1]Sheet1!$C$1:$C$4,MATCH(1,(B1=[workbook1]Sheet1!$A$1:$A$4)*(C1=[workbook1]Sheet1!$B$1:$B$4),0))

=index([Woorkbook1]Sheet1!$B$2:$L$1487,match(1,([Workbook1]Sheet1!$B$2:$B$1487=A2)*([Workbook1]Sheet1!$C$2:$C$1487=B2),0),11)

Thanks for all the tips everyone!  

The formula:

Matches first name and last name in columns A and B of workbook 2 to first name and last name in columns B and C of workbook 1 returning the value of column 11 (from the array in workbook 1) into workbook 2

Outputs a 0 for an empty cell in columns formatted for numbers (slightly annoying but I guess I can just change the formatting for that column =P) and returns all other numbers

Does not return anything for empty cells in text formatted columns (perfect) and returns any text found.

The only thing I encountered, and I don't know if this is normal, I had to change my workbook name so it had no spaces. So keep that in mind if you, humble internet user surfing the web for excel formulas, stumble across this one day an use it.

>>  I had to change my workbook name so it had no spaces

If the workbook name has spaces, you need to enclose the workbook and sheet name in single quotes

='[The workbook with spaces.xlsx]Sheet1'!$A$1

Excel will do that automatically if you click the cell instead of typing its address.

Hi,

Try this.  In cell E1 of sheet1 of Book2, enter this formula and copy down

=IFERROR(INDEX([Book1]Sheet1!E$2:E$5,MATCH(1,INDEX(([Book1]Sheet1!A$2:A$5=B2)*([Book1]Sheet1!B$2:B$5=C2),,),0),1),"")

Hope this helps.