Nesting Formulas - I think this is where I'm headed

[Samantha S]

Hello Chandoo & Gang,

Please bear with me, I'm not an expert with nesting so I hope I can explain this right. Is there away to get a formula/nested formula to provide data:

On one spreadsheet we have a list of center numbers that are unique (5215 records/rows) that is our starting point for a vLookup. The table array/range lives on a seperate tab and is made up of 2205 records/rows

Is something like this possible?
Does cell A2 (06316) from Tab 1 exist anywhere in range A1:E2205 on ss 2 and if so return value from E (5)?

I have a fairly good understanding of the vLookup. And realzie it is normally looking at a vertical range and in my experience it is ALWAYS looking at the left most column for the unique. However, you all are the experts so if there IS a way I'm looking to learn

Is there another formula I can use? Essentially what we are trying to do is:

If value on spreadsheet1 exists anywere in "this range of cells" on spreadsheet two, return this "value" from spreadsheet 2.

I've uploaded a simplified version of our spreadsheet.

 

[Luke M]

You could use this array formula:
=INDEX('SS2'!$E$3:$E$2206,MATCH(A2,INDEX('SS2'!$A$3:$C$2206,,MAX(COLUMN('SS2'!$A$2:$C$2)*('SS2'!$A$3:$C$2206=A2))),0))

Confirm formula with Ctrl+Shift+Enter, not just Enter. Copy down as needed. If formula displays #N/A, then value was not found in SS2.

How it works? The MAX function in inner part figures out which column of A:C contains our value. This then tells the INDEX which column to give to the MATCH. MATCH figures out which row our value was on, and tells the outer INDEX to go to the same row and get value from col E.

 

[Haseeb A]

Hello Samantha,

..or since your output is date, you could use, in C2 then copy down.

=IFERROR(SMALL(IF('SS2'!A$3:C$2206=A2,'SS2'!E$3:E$2206),1),"")

This is an Array Formula. So must be entered with CTRL+SHIFT+ENTER

 

[Samantha S]

There's a reason why I love this site! Thank you both very much for your help!