Pull Value of Cell from other Sheet IF

L. Barrett

I have tried this a variety of ways. I have Hotel and then 3 standard Vendor Types. I am checking to see if certain information has been entered for each of these on Sheet B. I have the formula pulling the information for these correctly. The issue is with any non-standard vendor types.

Since not all meetings will have an Other, I do not want it to look like the the information has not been entered if there is no information to enter. I want it to identify that there were not any that fit the criteria.

I have tried using a helper column. Return a Vendor Type value for any meeting id that does not match the three standard vendors, but could not get it to work.

Ideally this is what I want:

If Vendor Type on Sheet A = Other,

then look to Sheet B. If any of these (Translator/Caterer/Shredding/Other) appear as a Vendor Type on Sheet B when the Meeting ID on Sheet A = the Meeting ID on Sheet B,

then return the value of the Date or Text in a different cell from Sheet B into Sheet A.

If there are none of these (Translator/Caterer/Shredding/Other) on Sheet B with a matching Meeting ID then return No Other.

Thanks for any assistance.

KPH

Hi @L. Barrett

I am not sure I have understood your requirements but am pretty confident you need to use an IF with an INDEX COLLECT to return the correct row. I've broken down your requirements into steps and built the formula for each part so you can see how it is done and then adapt it, if this isn't quite what you are after. In each step I highlight the additions to the formula in bold. Your cross sheet references and column names will likely differ from mine.

Start with

If Vendor Type on Sheet A = Other,

which is

=IF([Vendor Type]@row ="Other",

Then look in Sheet B

which is

=IF([Vendor Type]@row = "Other", INDEX(

Collect the value from the date column

which is

=IF([Vendor Type]@row = "Other", INDEX(COLLECT({Sheet B Date Column},

Where the meeting ID on sheet B matches that on sheet A

which is

=IF([Vendor Type]@row = "Other", INDEX(COLLECT({Sheet B Date Column}, {Sheet B Meeting ID Column},[Meeting ID]@row,

And vendor type in Sheet B is translator or caterer or shredding or other

which is

=IF([Vendor Type]@row = "Other", INDEX(COLLECT({Sheet B Date Column}, {Sheet B Meeting ID Column}, [Meeting ID]@row , {Sheet B Vendor Type}, OR(@cell = "Translator", @cell = "Caterer", @cell = "Shredding", @cell = "Other")

Return the first matching row and nothing if the initial IF is false

which is

=IF([Vendor Type]@row = "Other", INDEX(COLLECT({Sheet B Date Column}, {Sheet B Meeting ID Column}, [Meeting ID]@row , {Sheet B Vendor Type}, OR(@cell = "Translator", @cell = "Caterer", @cell = "Shredding", @cell = "Other")), 1))

Then we just need to add an extra part to return "No Other"

If there are none of these (Translator/Caterer/Shredding/Other) on Sheet B with a matching Meeting ID

With the original formula if there was not a match, an "INVALID VALUE" error would appear, so we can use an IFERROR function to swap that message for "No Other".

like this

=IF([Vendor Type]@row = "Other", IFERROR(INDEX(COLLECT({Sheet B Date Column}, {Sheet B Meeting ID Column}, [Meeting ID]@row , {Sheet B Vendor Type}, OR(@cell = "Translator", @cell = "Caterer", @cell = "Shredding", @cell = "Other")), 1), "No Other"))

Here is what that looks like

I hope that helps.