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.

Date	Employee	Hours	Reason	Authorised By	Meeting Time	Team
Tab 1 CW
Name	Joe Bloggs1	Joe Bloggs2	Joe Bloggs3	Joe Bloggs4	Joe Bloggs5
Role Type	CM	CM	CM	CM	CM
Tab 2 WW
Name	Joe Bloggs6	Joe Bloggs7	Joe Bloggs8	Joe Bloggs9	Joe Bloggs10
Role Type	WW	WW	WW	WW	WW
Tab 3 CC
Name	Joe Blogs11	Joe Blogs12	Joe Blogs13	Joe Blogs14	Joe Blogs15
Role Type	CC	CC	CC	CC	CC

<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> </tbody>