HLOOKUP multiple table arrays HOW DO I DO THIS? Pls help

[steduffy]

Hi Guys,

I have a HLOOKUP formula that looks at a table on a separate tab. I have 2 further tables in the same format but both on different tabs which I also want the formula to read. Basically the lookup value element of the formula contains all of the possibilities within those 3 seperate tables. Can someone help me on what I need to do to make this work. The current formula is:

=IFERROR(HLOOKUP(B133,CW!$1:$2,2,0),"")

the other 2 tabs are called WW and CC but where do I enter them within the formula so that it reads all 3? hope this makes sense.

Thank you

 

[Special-K99]

You havent explained this clearly.
Im assuming if its not on the first table you want to search the second, if not on the second table you want to search the third, yes?

=IFERROR(HLOOKUP(B133,CW!$1:$2,2,0),IFERROR(HLOOKUP(B133,WW!$1:$2,2,0),IFERROR(HLOOKUP(B133,CC!$1:$2,2,0),"VALUE DOES NOT EXIST ON ANY TABLE")))

 

[steduffy]

Hi thanks for your response. I tried that below and it doesnt work. So hopefully this will make more sense. So the formula is currently as I put in the initial thread in the yellow box and just looks at tab1 (and works fine). As we have 2 new teams (they have to be seperate tabs) I want the formula to identify in the TEAM based on what Employee name is in the EMPLOYEE collumn.


[TABLE="width: 522"]
<colgroup><col width="64" style="width: 48pt;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;"> <col width="140" style="width: 105pt; mso-width-source: userset; mso-width-alt: 5120;"> <col width="124" style="width: 93pt; mso-width-source: userset; mso-width-alt: 4534;"> <col width="106" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3876;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Date[/TD]
[TD="width: 86, bgcolor: transparent"]Employee[/TD]
[TD="width: 78, bgcolor: transparent"]Hours[/TD]
[TD="width: 96, bgcolor: transparent"]Reason[/TD]
[TD="width: 140, bgcolor: transparent"]Authorised By[/TD]
[TD="width: 124, bgcolor: transparent"]Meeting Time[/TD]
[TD="width: 106, bgcolor: transparent"]Team[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Tab 1 CW[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name[/TD]
[TD="bgcolor: transparent"]Joe Bloggs1[/TD]
[TD="bgcolor: transparent"]Joe Bloggs2[/TD]
[TD="bgcolor: transparent"]Joe Bloggs3[/TD]
[TD="bgcolor: transparent"]Joe Bloggs4[/TD]
[TD="bgcolor: transparent"]Joe Bloggs5[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Role Type[/TD]
[TD="bgcolor: transparent"]CM[/TD]
[TD="bgcolor: transparent"]CM[/TD]
[TD="bgcolor: transparent"]CM[/TD]
[TD="bgcolor: transparent"]CM[/TD]
[TD="bgcolor: transparent"]CM[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 2"]Tab 2 WW[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name[/TD]
[TD="bgcolor: transparent"]Joe Bloggs6[/TD]
[TD="bgcolor: transparent"]Joe Bloggs7[/TD]
[TD="bgcolor: transparent"]Joe Bloggs8[/TD]
[TD="bgcolor: transparent"]Joe Bloggs9[/TD]
[TD="bgcolor: transparent"]Joe Bloggs10[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Role Type[/TD]
[TD="bgcolor: transparent"]WW[/TD]
[TD="bgcolor: transparent"]WW[/TD]
[TD="bgcolor: transparent"]WW[/TD]
[TD="bgcolor: transparent"]WW[/TD]
[TD="bgcolor: transparent"]WW[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Tab 3 CC[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Name[/TD]
[TD="bgcolor: transparent"]Joe Blogs11[/TD]
[TD="bgcolor: transparent"]Joe Blogs12[/TD]
[TD="bgcolor: transparent"]Joe Blogs13[/TD]
[TD="bgcolor: transparent"]Joe Blogs14[/TD]
[TD="bgcolor: transparent"]Joe Blogs15[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Role Type[/TD]
[TD="bgcolor: transparent"]CC[/TD]
[TD="bgcolor: transparent"]CC[/TD]
[TD="bgcolor: transparent"]CC[/TD]
[TD="bgcolor: transparent"]CC[/TD]
[TD="bgcolor: transparent"]CC[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]