Lookup two conditions (equals and not blank)

[tomlancaster]

I was wondering if anyone could help me with a formula.

I have 3 columns with a list of data in Sheet 1

Column A = Username
Column B = Date Issued
Column C = Serial

I have 2 columns in Sheet 2

Column A = Username
Column B = Serial

I would like to use the data in Sheet 1 to import the Serial number in Sheet 2

This is how I think I need to achieve the right result:

SHEET 2 COLUMN B:
If “Username” (SHEET 1) = “Username” (SHEET 2) AND If “Date Issued” (SHEET 1) is not Blank, then import “Serial Number” (SHEET 1) from the same row.

I was hoping to achieve this using an Index and Match function but I am not successfully getting any data.

[NBVC]

for first match,

=INDEX(Sheet1!$C$2:$C$100,MATCH(1,INDEX((Sheet1!$A$2:$A$100=A2)*ISNUMBER(Sheet1!$B$2:$B$100),0),0))

or

for last match

=LOOKUP(2,1/(Sheet1!$A$2:$A$100=A2)*ISNUMBER(Sheet1!$B$2:$B$100),Sheet1!$C2:$C$100)

adjust ranges to suit.

Note: If only 1 match, then both formulas should get same result.

[tomlancaster]

Thanks for your reply.

I do actually require the last match so I tried the LOOKUP function but this didn't return the right result. There are 3 serial numbers in the other sheet for each user entry but the one I am after has a date issued value in another column.

The formula I used was:

=LOOKUP(2,1/(PROTON!$C$1:$C$9999=B4)*ISNUMBER(PROTON!$K$1:$K$9999),PROTON!$AA$1:$AA$9999)

I did try the INDEX function and that worked great but returned the first entry which is not what I need as sometimes new devices are issued with different serial numbers.

How can I get the LOOKUP to make sure that it is only returning the value if both criteria on the same line are met?

[NBVC]

I was missing a pair of parentheses.


=LOOKUP(2,1/((PROTON!$C$1:$C$9999=B4)*ISNUMBER(PROTON!$K$1:$K$9999)),PROTON!$AA$1:$AA$9999)

[tomlancaster]

Thanks that worked

[NBVC]

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save