Comparing a cell in sheet 1 to columns in multiple sheets

**chrisdua12** · 06-10-2016, 07:23 PM

So essentially what I am trying to do is take the information from a cell in a column on one sheet, compare that information to a column in several other sheets in order to find a match, then post a value from part of the row the match was found on based on that match. Does that make sense?

**Tony Valko** · 06-10-2016, 07:31 PM

Originally Posted by chrisdua12

Does that make sense?

Sure. Now, give us more specific details!

What are the sheet names?

Will the lookup value only be found on one sheet?

Where do we search for the lookup value?

Once we find the lookup value where do we get the result you want?

**chrisdua12** · 06-10-2016, 07:47 PM

Lets say sheet 1 is "All Files"
2 "Main site Docs."
3 "Dist. Docs."
4 "Intl. Docs."
5 "Spec. Docs."

I am looking at a cell in column "A" of "All Files" for the lookup value.
I want to compare this value to sheets 2-5 column A Through the whole column Except row 1 (Theoretically there should only be one matching value). Then the value I want to post will be in Column "B" of that matching value. And I want to post this into column "D" or 4 of "All Files"

**Tony Valko** · 06-10-2016, 08:12 PM

A5 = lookup value

Try this array formula**:

=VLOOKUP(A5,INDIRECT("'"&INDEX({"Main site Docs.","Dist. Docs.","Intl. Docs.","Spec. Docs."},MATCH(TRUE,COUNTIF(INDIRECT("'"&{"Main site Docs.","Dist. Docs.","Intl. Docs.","Spec. Docs."}&"'!A:A"),A5)>0,0))&"'!A:B"),2,0)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Here's a small sample file that demonstrates this.