# Vlookup across Multiple Sheets with multiple results

• Re: Vlookup across Multiple Sheets with multiple results

12 results on the Function sheet, 9 on the new Sheet4 (with the new range) and 12 on the old Sheet4.

• Re: Vlookup across Multiple Sheets with multiple results

Ok give me the basic formula you are using on sheet4

• Re: Vlookup across Multiple Sheets with multiple results

Code
1. =IFERROR(INDIRECT(CONCATENATE("'",Function!\$A\$8,"'!A",SMALL(Function!\$F\$2:\$F\$1500,ROW()-1))),"")
• Re: Vlookup across Multiple Sheets with multiple results

Select you first cell on sheet4 and highlight in the formula bar
CONCATENATE("'",Function!\$A\$8,"'!A",SMALL(Function!\$F\$2:\$F\$1500,ROW()-1))
and press F9. (press Ctrl+Z to return it back to the formula)
When you press F9, you should see something like 'Sheet1'!A14
If you selected the first cell, you should see the number after the column letter (e.g. 14) should be the first number of the function sheet on the column that prints out the row numbers of matches.

Is this true?
Also \$F\$2:\$F\$1500 might have to be edited to fit you column of row numbers.

• Re: Vlookup across Multiple Sheets with multiple results

Yes... A114 was displayed. However, A5, A6, and A7 have been skipped.

• Re: Vlookup across Multiple Sheets with multiple results

I did some testing. In one formula, there has to be something that tells it to start searching on A2. I verified that because on the new Sheet4, I pasted the formula and a result appeared.

• Re: Vlookup across Multiple Sheets with multiple results

Did you say that you moved the function formulas down a few rows?

• Re: Vlookup across Multiple Sheets with multiple results

On Sheet4 I did. They start on A5:E5. The "Find' box is now B2. Every time I search, I noticed that the first 3 finds are not shown, but display in the Function tab.

• Re: Vlookup across Multiple Sheets with multiple results

On sheet4 change the formula from
ROW()-1
to
ROW()-4

EXPLANATION: ROW() gets the row number of the cell it is in. Since you started in A5 the row number is 5 minus 1 is 4. So that 4 is passed to the small function saying find the 4th smallest number in it's range. So it would always skip over the first 3 matches

• Re: Vlookup across Multiple Sheets with multiple results

You are once again, a life saver! Thank you.

• Re: Vlookup across Multiple Sheets with multiple results

I'm feeling a little creative/curious.

Do you know of a way to offer a secondary search (placed underneath the primary on sheet4) that would filter through the existing results found in the 'D' column?

• Re: Vlookup across Multiple Sheets with multiple results

Are you talking about doing another search on the searched material?

• Re: Vlookup across Multiple Sheets with multiple results

Yes. Originally, I just wanted to apply a filter, but that didn't fly so well. Can a search be applied to the searched options? It needs to target the D column, but I am unsure how to even start.

• Re: Vlookup across Multiple Sheets with multiple results

Ok, explain to me what the search will do? Will this search be optional? How or what are we searching for? What is being searched through?

• Re: Vlookup across Multiple Sheets with multiple results

The D column contains specifics that pertain to the search results. Column A holds the search results, B and C arent related to this request, but everyone wants to filter the things that are in column D.

The secondary search would be completely optional and would contain words or specific abbreviations, not special characters or numbers or partial words.

• Re: Vlookup across Multiple Sheets with multiple results

Where will this value be place to do the secondary search? How will we determine to do the second search or not? Are we searching for partial matches again or full matches?

• Re: Vlookup across Multiple Sheets with multiple results

The additional search 'box' would be in B3. The secondary search would be optional and only used if the person wants to utilize it. Otherwise, it will be blank until something is inserted.

I'm thinking that complete words would be best for this secondary function.

• Re: Vlookup across Multiple Sheets with multiple results

Here you go!

## Files

• Re: Vlookup across Multiple Sheets with multiple results

Wow, that was fast!

As far as modification goes, what exactly do I need to change? Is it still a complete drag n' drop as before?

Thank you so much.

• Re: Vlookup across Multiple Sheets with multiple results

If I understand what you are saying, yes