Help with lookup formula that returns whole columns

Hi, I apologize for my ignorance on this topic, but this is definitely out of my knowledge base. I'm working on a spreadsheet (Book1 is hopefully attached), and I'm trying to return the data on sheet 2 to sheet 1 based on the lookup value typed into cell D2. It's meant to be an approximate lookup to where if you only give it a little bit of data, then it will return the whole range of data on sheet 2 since the first four numbers would be matching. I really don't even know where to start with this so again, I apologize if I didn't quite word any of it right.

Ignore this post

Special-K, thank you for the help. Unfortunately, it did not seem to work.

Hi, CARACALLA. I entered that formula into A5 as shown, then I hit Ctrl+Shift+Enter. It didn't work. Essentially, the value in H2 (with wildcard symbol) needs to return the data from sheet 2. Now, there will be more data ranges like what is currently on sheet 2 which is why H2 needs to be the lookup. when that lookup changes, another data range will pop up(hopefully).

Try this

A5=IFERROR(INDEX(Sheet2!A$2:A$61,SMALL(IF(ISNUMBER(SEARCH(Sheet1!$D$2,Sheet2!$A$2:$A$61)),ROW(Sheet2!A$2:A$61)-ROW(Sheet2!A$2)+1),ROWS(Sheet2!A$2:Sheet2!A2))),"")


Control +shift +enter


copy across and down

Awesome! That works to where it populates the data now. My only issue is, whenever you delete the lookup value in D2, the data doesn't disappear kind of like a VLOOKUP with an IFERROR. No lookup = no data returned. Basically the ability to change the lookup in D2 from 0502 to things like 0503,1511, etc. There will be data on the other sheet that will match that starting criteria, but when you change the lookup in D2, the data returned down below it needs to change as well.

A5=IF($D$2<>"",IFERROR(INDEX(Sheet2!A$2:A$61,SMALL(IF(ISNUMBER(SEARCH(Sheet1!$D$2,Sheet2!$A$2:$A$61)),ROW(Sheet2!A$2:A$61)-ROW(Sheet2!A$2)+1),ROWS(Sheet2!A$2:Sheet2!A2))),""),"")


Control +shift+enter

Sweet!!!! It works beautifully now! I apologize for not fully giving all of the necessary info you might have needed up front, but I wasn't sure how I needed to word everything.

Thank you very much!!

You are welcome