Very Detailed IFs and VLOOKUPs Help Needed

[jamieleeuk]

Or it could be solved through VBA but in any case i'm well and truly stuck and I need to get this project finished by Thursday (Meeting the boss and plan to take this with me) so therefore any help is much appreciated.

I have a copy of the spreadsheet (with the sensitive info amended to random but sensible figures) available to upload somewhere / send to someone.

In brief, I have 2 "Database Tabs" Each holding the same layout and details except one factor, the numbers on Tab 2 are slightly higher than Tab 1 (purposefully).
On another tab, there's 3 cells a user MUST fill in (select from data validation list) and once all three are filled in, the formula should run - the cells are B3, B5 and H5 and the logic is something like this:

If cell B5 = London UK and H5 = London UK, Lookup B3 in Tab 2, go across X columns and return the data in that cell.

Problem is, I have more than 7 possibilities for cell B5 and H5 therefore ending the possibility of using IF statements.

As I said, I have a copy of the spreadsheet which I am willing to upload or send to someone and I think you'll probably need to see it to know what I want (and I have detailed a lot clearer what it is I need on the spreadsheet in a small table).

If someone is willing to help, please could you either:

A Let me know of somewhere I can upload where you can view or
B Let me have an email address I can fwd it to you.

You guys have helped me in the past and i'm hoping you can help me again

Kind Regards

Jamie

 

[Jonmo1]

Problem is, I have more than 7 possibilities for cell B5 and H5 therefore ending the possibility of using IF statements.

How many?

And, Do B5 and H5 have to be the same? or do you have like a list and B5 and H5 have to be in that list, but not necessarily both the same?

And does "X columns" vary depending on what values are in B5 and/or H5 ?

 

[jamieleeuk]

Thanks for replying

The list validatin B5 and H5 are the same, but they do not have to be the same. If one of them refers to LONDON UK (one of the list items) then the formula will look up in the LONDON tab. Otherwise the formula will lookup in the REGIONS tab.

"X" columns is just however many columns across the vlookup formula (if this is what was used) would need to move along to get to the correct cell.

IE, Some columns are "UK Law on EUR" "UK Law on USD" "UK Law on SGD"

If B5 was "LONDON UK" and H5 was "Singapore" and B3 was "Partner" I would want the formula to search for the word "Partner" in the LONDON tab and scroll across X columns "where X = the amount of columns it takes to get to the specific column" in this example it would be until you get to the column headed UK Law on SGD.

I had 7 IF statements before, but I have since added another jurisidiction code so IFs are out the window (I think).

Kind Regards

 

[Jonmo1]

OK, this is very difficult to follow what you're trying to do.
But one thing I think I understand is that

EITHER B5 OR H5 must be within your list, then it does the lookup on the London Tab. Otherwise do the lookup on the Regions tab.

That can be done pretty easily, if you put your "List" in a range say Z1:Z100..

=VLOOKUP(B5,IF(OR(ISNUMBER(MATCH(B5,Z1:Z100,0)),ISNUMBER(MATCH(H5,Z1:Z100,0))),London!A1:Z100,Regions!A1:Z100),2,FALSE)

Does that help?

 

[jamieleeuk]

Thanks but not worked:

B5 and H5 can only be one of the following:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=64 height=19>

• London UK

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>

• Regions UK

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>

• America

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>

• Dubai

</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 19.5pt; BACKGROUND-COLOR: transparent" height=26>

• Asia Pacific

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>

• Euro

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>

• CNY

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>

• Singapore

</TD></TR></TBODY></TABLE>
B3 (The bit to lookup) can only be a specific entry as well but there's too many to list here (about 30 - 40 different titles) from Partner to Trainee etc.

Tabs are called "1 June 2009 - REGIONS" and "1 June 2009 - LONDON"

Those 2 tabs are identical in the layout in that column G on REGIONS is the same column as G on LONDON. The difference being that the LONDON values are slightly higher but the layout is identical.

The column headers begin at A6 right through to AD6

The actual Data doesn't start til a few rows below that (A10:AD56)

Again, the layout is identical on both above named tabs.

I amended your code

=VLOOKUP(B5,IF(OR(ISNUMBER(MATCH(B5,Z1:Z100,0)),ISNUMBER(MATCH(H5,Z1:Z100,0))),London!A1:Z100,Regions!A1:Z100),2,FALSE)

to

=VLOOKUP(B5,IF(OR(ISNUMBER(MATCH(B5,'1 June 2009 - REGIONS'!B10:AD55,0)),ISNUMBER(MATCH(H5,'1 June 2009 - LONDON'!B10:AD55,0))),'1 June 2009 - LONDON'!B10:AD55,'1 June 2009 - REGIONS'!B10:AD55),2,FALSE)

but got N/A error.

Here's a snapshot of part of the Regions Tab so you can see what I mean by finding the word "Partner" and going across, say, 4 columns and returning that data:

Thanks for the time you're putting in here.

 

[jamieleeuk]

OK JonMo (and anyone else who is taking an interest in this) - I have amended your code further to

=VLOOKUP(B3,IF(OR(ISNUMBER(MATCH(B5,'1 June 2009 - LONDON'!B10:AD55,0)),ISNUMBER(MATCH(H5,'1 June 2009 - REGIONS'!B10:AD55,0))),'1 June 2009 - LONDON'!B10:AD55,'1 June 2009 - REGIONS'!B10:AD55),2,FALSE)

which now gives me a numerical value - but the value doesn't change with the selections in B5 and H5. Part of it works - IE, it's looking up the B3 value (on User tab) in the REGIONS Tab only, and is only going to the 2nd column (so still H5 and B5 on user tab are not having an effect on the outcome when amended).

Would you mind taking a look at the spreadsheet?

 

[Jonmo1]

Match can only search a 1 dimensional range (either 1 row, or 1 column).

you have them searching a 2D range
MATCH(B5,'1 June 2009 - LONDON'!B10:AD55

So the list that holds the possible matches for B5 or H5 MUST be a 1Dimensional range. 1 column OR 1 row.

 

[jamieleeuk]

HI JonMo,

I managed to find a way of doing this and it works perfectly. I would like to thank you for taking the time to help.

I added another sheet and added all the eventualities of combinations of B5 and H5 and Indexed them, then used the sumproduct

SUMPRODUCT((Sheet1!A1:A40=B5)*(Sheet1!B1:B40=H5),(Sheet1!C1:C40))

If you would like to see it, I'm more than happy to send you a sample of the spreadsheet.

Kind Regards