Lookup up an entire table not just a coloumn in a table like Vlookup does

**jmzis4me** · 06-05-2014, 05:53 PM

Hello, I'm trying to see if you can look up multi columns for a number and when it finds it return back to one column and return that data?

For understanding:

I am trying to sort out territories for a state I work on; the territories are divided up by zip codes.

i.e.
A B C D

territory 1 12345 54321 11222
territory 2 22222 33333
territory 3 44444 55555

and what I want is to say put a formula in cell B3 on Sheet 1 pointing to cell C3 that has zip code 33333. I want it to then take that and look in Sheet 2 and search A1:D3 and when it finds 33333, return "territory 2" back to cell B3 on Sheet 1.

Does that make sense? Is that even possible in excel?

Thanks!!

**k64** · 06-05-2014, 06:15 PM

Try this

Formula:

Please Login or Register  to view this content.

**jmzis4me** · 06-05-2014, 06:28 PM

update: scratch that, I think I have just found a loophole around what I'm trying to do. thanks for the help!

--------------------------------------------------

unfortunately that didn't seem to work. would you mind quickly explaining to me the formula? It could work brilliantly but b/c I don't totally understand, transposing it from my example to the real thing (much larger dataset) could be where I went wrong.

Thanks!

**k64** · 06-05-2014, 06:59 PM

I don't have access to your real dataset, but if you create your example, where Sheet2!A1:D3 has the data you posted and Sheet1!C3 contains the value you want to look up, my formula should work. Try that and then let me know if it's working.