Excel - Combine VLOOKUPs Using IF Function

[Mictain]

Hi All,

I am currently using a VLOOKUP formula on a weight against price spreadsheet and it works well, but I need to expand it so it looks at three column sets and pulls the required information from the correct one depending on which data is in another column.

At the moment I am using:

=VLOOKUP(I7,Rates!$A$1:$B$17,2,TRUE)

Where I7 is the item weight and A1:B17 gives the weight to price breakdown.

What I need to do is add another column on the main sheet and input A, B or C per item, depending on which type of packaging is required. And each type will have its own price breakdown table.

So I need the formula to say:

If the cell=A then go to lookup table 1 and pull that info, if the cell=B then go to lookup table 2, if the cell=C then go to table 3.

All in one formula.

I'm sure it's simple stuff for all you Excel gurus out there but I can't seem to suss the stringing-it-all-together bit!

Any ideas appreciated.

Thank you

Neil.

 

[SkipVought]

Hi,
[tt]
=VLOOKUP(I7,if(I7=A,Table_1,if(I1=B,Table_2,Table_3)),2,TRUE)
[/tt]
just to answer your immediate question.

But often, there are other ways, if the data can be better understood. Please post samples of your data tables and how they relate in your workbook.

Are these tables entirely physically separate or are there shared columns?

Is the data logical or tabular? How does the lookup value relate to the table?


Skip,
[sub]
[red][/red]
[/sub]

 

[Mictain]

Thanks for the quick reply, Skip. With a little bit of tweaking that formula setup worked. Cheers

This one works:

=VLOOKUP(I7,IF(J7="A",Rates!$A$3:$B$4,IF(J7="B",Rates!$D$3:$E$7,Rates!$G$3:$H$9)),2,TRUE)

As you asked for a sample...

In this shot I start with the item weight, which I need to sort into a category - A, B or C - and this interrogates one of the three lookup tables for a weight based upon that category:

http://www.boomspeed.com/mictain/ExcelFormat.jpg

The lookup then grabs the price from the correct table; each of which would follow this format for weight and price breakdown: (they are not shared)

http://www.boomspeed.com/mictain/ExcelTable.jpg

And that's it. It's a very simple setup, I just couldn't put it together!

If there is another way to do this sort of thing it would be useful to know. It might stop me asking daft questions on here!

Thanks again.

Neil.

 

[SkipVought]

Your screen shots were meaningless isolated chunks.

What does Rates!A3:H9 look like?

It nearly seems that you might could use an HLOOKUP outside the VLOOKUP and possibly an OFFSET.


Skip,
[sub]
[red][/red]
[/sub]

 

[Mictain]

Rates!A3:H9 - Same format as the original shot but with three isolated tables:

http://www.boomspeed.com/mictain/ExcelTable2.jpg

HTH

Neil.

 

[SkipVought]

[TT]
A 0 0.32
A 100
B 0 0.44
B 101 0.65
B 251 0.9
B 501 1.3
B 750
C 0 1
C 101 1.27
C 251 1.7
[/TT]
one table, one formula
[tt]
=offset(rates!$A$3,match(i7,rates!$a$3:$A$9,0),2,counta(rates!$a$3:$A$9,i7),1)
[/tt]


Skip,
[sub]
[red][/red]
[/sub]

 

[Mictain]

Thanks Skip. I'll have a go with that one later

 

[SkipVought]

BTW,

It is NOT best practices to have separate tables for similar data.

It should not be a matter of preference using table 1, table 2 or table 3 versus a single table. It is a BAD design and a BAD practice.

Skip,
[sub]
[red][/red]
[/sub]

 

[Mictain]

I was initially trying to work around the formula I had, and I have not used Offset before so was unaware this kind of setup could be achieved.

Now I am I shall endeavour to use that instead where applicable.

Thank you for your input.