Return Info from different tab, from second set of matched data

[SSwwet2018]

I'm working on a Bill of Materials master file and trying to set up a Cover Sheet that will allow us to Input the BOM# and return the information we want to see.
I Have the formula set up for identifying the right tab, and bringing back the different information we want (Batter, filling, crust, topping), but some items have 2 fillings, or 2 toppings. I can not get the formula to return me the information from the second filling or topping.


See next post by me for the files

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[SSwwet2018]

Ahh, thank you.
here is the sample file.

[SSwwet2018]

Bump, anyone have some insight on how to do this?

[AliGW]

Sorry - I did look at the time, but couldn't make head nor tail of what you are trying to do.

[SSwwet2018]

Trying to get my formula to return me the second match, instead of the first.

Right now it will give me the first Filling

I need Filling 2, to return me the second filling data from the other tab

[SSwwet2018]

Solved it!!!!!!!!!!!!!!!!!!!!!!

[AliGW]

Great! Please, for the benefit of others and edification of all, tell us how you did it.

[SSwwet2018]

A normal Vlookup with indirect works for the first match
The second match had to use an Index instead.

=IFERROR(INDEX(INDIRECT("'"&$A$1&"'!$B:$B"),SMALL(IF(LEFT($B$12,2)=LEFT(INDIRECT("'"&$A$1&"'!$A:$A"),2),ROW(INDIRECT("'"&$A$1&"'!$A:$A"))-ROW($A$1)+1),2)),"-")

This is the actual formula used. Broken down...

By using the Indirect function you can replace the 'TAB' in all the formulas so that your input into A1, becomes the Tab name. - solved the way to pull from different tabs without changes the formula or using find and replace.
Due to the information i had to work with i was only able to match the first 2 characters of the data i was using (filling was f-###, and the second filling was f-###) - By using the Left(B2,2)=Left(indirect....) gave the information to search for.
The small(if(),Row-Row, 2) was used to pull the nth "2nd" match into the index as the row # to look into.

Indirect("'"&$A$1&"'!$B:$B") = The tab and column you want the data to pull from.


This may be a bit confusing, but I built up to the final formula. Started with all data on the same tab, to get basic functionality working.
Moved my data to a different tab, and adjust to have formula pull from another tab
Replace ('Tab'!Column) with the indirect function to have the formula do the work of matching tab.

[AliGW]

Thanks so much!