Vlookup search 2 ranges depending if the reference cell contains certain text

[ElsiE93]

Hi guys,I'm trying to create a formula that will correctly search either of 2 ranges of data depending on if the reference cell contains certain text. Both ranges are on 2 different tabs to the summary tab where my reference cell is.My 2 ranges are:Input Corby Picker Data (Range) A:FInput GT BP Picker Data (Range) A:FI want a formula that will look up the value in my G column and if the cell contains 'CY10' then search in the Input Corby Picker Data tab using the value in my A column. If not, use the range in the GT BP Picker Data tab. Data to be extracted is in the 4th column of both ranges of data and I want an exact match.I have tried the formula below but get #Value !. Any help you could give me would be greatly appreciated! =VLOOKUP(A14,IF(G14&"CY10",'Input Corby Picker Data'!A:F,'Input GT BP Picker Data'!A:F),4,FALSE)

 

[Eric W]

You are very close, try:

=VLOOKUP(A14,IF(COUNTIF(G14,"*CY10*"),'Input Corby Picker Data'!A:F,'Input Gt BP Picker Data'!A:F),4,FALSE)

 

[ElsiE93]

Brilliant Eric, it works! Thank you so much

 

[Eric W]

You're welcome!