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

steduffy

New Member
Joined
Jan 9, 2019
Messages
2
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
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

New Member
Joined
Jan 9, 2019
Messages
2
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.


DateEmployeeHoursReasonAuthorised ByMeeting TimeTeam
Tab 1 CW
NameJoe Bloggs1Joe Bloggs2Joe Bloggs3Joe Bloggs4Joe Bloggs5
Role TypeCMCMCMCMCM
Tab 2 WW
NameJoe Bloggs6Joe Bloggs7Joe Bloggs8Joe Bloggs9Joe Bloggs10
Role TypeWWWWWWWWWW
Tab 3 CC
NameJoe Blogs11Joe Blogs12Joe Blogs13Joe Blogs14Joe Blogs15
Role TypeCCCCCCCCCC
<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>


 

Forum statistics

Threads
1,081,680
Messages
5,360,498
Members
400,588
Latest member
SpannersWatson

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top