Hi there I'm trying to do what I think should be a fairly simple lookup. I have 3 arrays, only one of which will have the data I'm looking for. I have used the following formula: =IF(ISNA(VLOOKUP(A2,Tip,5,FALSE)),VLOOKUP(A2,KD,4,FALSE),"") which as you can see looks in the array named "Tip" and if the information isn't there, looks in the array named "KD". I also need to look in an array called "ND" if the text from A2 isn't found in either "Tip" or "KD". Please can someone help? Thanks Cheryl

You didn't say what column to return for Array KH, so I chose 3. Two ways to do it: Note: I broke the formula apart with line feeds so they would fit in the response neatly, so you may have to put them back together when you paste them into Excel. One says "If the VLOOKUP doesn't return #N/A for a given array, then it must be in that array." =IF(NOT(ISNA(VLOOKUP(A2,Tip,5,0))),VLOOKUP(A2,Tip,5,0), IF(NOT(ISNA(VLOOKUP(A2,KD,4,0))),VLOOKUP(A2,KD,4,0), IF(NOT(ISNA(VLOOKUP(A2,KH,3,0))),VLOOKUP(A2,KH,3,0),""))) The other says "If VLOOKUP for Array1 returns #N/A, then look in Array2. If that returns a #N/A, the look in Array 3. If that returns a #N/A, then return "". However, if Array3 didn't return a #N/A, it must be in there, similar for Array2 and Array1 working backwards through the value_if_false for each nested if. =IF(ISNA(VLOOKUP(A2,Tip,5,0)), IF(ISNA(VLOOKUP(A2,KD,4,0)), IF(ISNA(VLOOKUP(A2,KH,3,0)),"", VLOOKUP(A2,KH,3,0)), VLOOKUP(A2,KD,4,0)), VLOOKUP(A2,Tip,5,0)) The NOT formula is a little longer, but it's more straightforward since it just works it's way through the 3 arrays until it finds the lookup_value - or not.

Absolutely fantastic! For some reason the second one won't return the value if it's in tip, but the first one works perfectly. You are a star DerbyDad03, thank you very much.

Glad I could help. BTW... I pasted the 2nd formula directly from my response into a new spreadsheet - didn't even take out the line feeds - then created the named ranges and it found the lookup_value in all three ranges.