Multiple IF statements, with VLOOKUP?

[tsimpson]

Big picture: I'm in sales, and the workbook I'm creating is for quotation purposes. I'd like to be able to enter the configured part number into one cell, then have the sheet spit out the expanded descriptions of what that part number represents. Our part numbers have a minimum of 9 characters, up to a maximum of 17 characters. Each position in that string of alphanumeric characters represents a specific thing (1-2 represent product code, 3-4 material code, 5 size, 6 series, and so on). Thanks to this site, I was able to figure out how to utilize the string function to extract the codes from their respective positions within the part numbers. I've also figured out how to put tables in a separate sheet within the same workbook and utilize the VLOOKUP function to have it spit out a description next to the codes. Works great!

There's only one problem... With the exception of positions 10 & 11, all of the codes which make up our part numbers, have only one possible definition. So for all but positions 10 & 11, I can simply have excel look and see what the code is for that position, and spit out the correct definition.

However, positions 10 & 11 make things difficult for me, as the characters in those positions have different definitions, based off the product code (positions 1 & 2 in the part number). The products I'm selling, are valves. So for example, if the product code (valve type) is a flush tank valve, then positions 10 & 11 represent the weld pad options. But if the product code (valve type) is a multiport or diverter type valve, then positions 10-11 represent the flow pattern.

So basically, when it gets to the description for positions 10-11, I need a formula which will tell excel to look at the product code in positions 1-2, and know that if that product code is any of our flush tank variety, then look in the "FT" table to find the description. If the product code is a diverter type valve, it needs to look in the "DP__MP" table for description. If the product code is any of our other type of valves, it needs to look in the "general" table to find a description.

I hope this makes sense the way I'm explaining it. I'd attach some pictures, but I can't figure out how. I found a few threads on this site which talked about multiple IF statements with VLOOKUP, but I couldn't get any of those solutions to work.

Here is the code I'm successfully using. Note that product, material, size, series, ends, seals, ball, and operator, are all tables with their respective codes and descriptions. Those lines work great! It's the code for C24 which I can't figure out.

=IFERROR(VLOOKUP(C18,PRODUCT,2,FALSE),"")
=IFERROR(VLOOKUP(C19,MATERIAL,2,FALSE),"")
=IFERROR(VLOOKUP(C20,SIZE,2,FALSE),"")
=IFERROR(VLOOKUP(C21,SERIES,2,FALSE),"")
=IFERROR(VLOOKUP(C22,ENDS,2,FALSE),"")
=IFERROR(VLOOKUP(C23,SEALS,2,FALSE),"")
*multiple IF statements, with VLOOKUP?
=IFERROR(VLOOKUP(C25,BALL,2,FALSE),"")
=IFERROR(VLOOKUP(C26,OPERATOR,2,FALSE),"")

Additional detail on the multiple IF statements, is as follows:
When looking to fill in the descriptions for positions 10-11 in my part numbers, if the product code is DI, DC, DD, DP, MP or MI, then VLOOKUP needs to reference table "DP__MI" for the description. If the product code is AF, FD, FT, FI or FC, then VLOOKUP needs to reference table "FT" for the description. If the product code is anything else (AN, CN, CP, SP, SD, TI, IM, FR, PV, CS, CT, SI), then VLOOKUP needs to reference the table "general" for descriptions.

 

[gaz_chops]

Can you upload sample data to dropbox and post the link here?

 

[tsimpson]

Can you upload sample data to dropbox and post the link here?

I uploaded it to google drive, here's a link: https://drive.google.com/file/d/0B7jlEtCeshT0cWxZbU9MekdlYTQ/view?usp=sharing

 

[gaz_chops]

Create another table with the values in e.g.

[TABLE="width: 122"]
<tbody>[TR]
[TD="class: xl70, width: 61"]AN[/TD]
[TD="class: xl69, width: 61"]General[/TD]
[/TR]
[TR]
[TD="class: xl70"] CN[/TD]
[TD="class: xl69"]General[/TD]
[/TR]
[TR]
[TD="class: xl70"] CP[/TD]
[TD="class: xl69"]General[/TD]
[/TR]
[TR]
[TD="class: xl70"] SP[/TD]
[TD="class: xl69"]General[/TD]
[/TR]
[TR]
[TD="class: xl70"] SD[/TD]
[TD="class: xl69"]General[/TD]
[/TR]
[TR]
[TD="class: xl70"] TI[/TD]
[TD="class: xl69"]General[/TD]
[/TR]
[TR]
[TD="class: xl70"] IM[/TD]
[TD="class: xl69"]General[/TD]
[/TR]
[TR]
[TD="class: xl70"]AF[/TD]
[TD="class: xl69"]FT[/TD]
[/TR]
[TR]
[TD="class: xl70"] PV[/TD]
[TD="class: xl69"]General[/TD]
[/TR]
[TR]
[TD="class: xl70"] CS[/TD]
[TD="class: xl69"]General[/TD]
[/TR]
[TR]
[TD="class: xl70"] CT[/TD]
[TD="class: xl69"]General[/TD]
[/TR]
[TR]
[TD="class: xl70"] SI[/TD]
[TD="class: xl69"]General[/TD]
[/TR]
</tbody>[/TABLE]

Test this in D24
=VLOOKUP(C24,INDIRECT(VLOOKUP(C18,'Ordering Chart'!Z1:AA12,2,FALSE)),2,FALSE)

 

[tsimpson]

Thanks for your prompt responses this morning. Unfortunately, although I think we're on the right track, it's not quite working yet. Here's the formula I created for D24:

=VLOOKUP(C24,INDIRECT(VLOOKUP(C18,'Ordering Chart'!Y1:Z22,2,FALSE)),2,FALSE)

When I left the part number unchanged (from the file I uploaded to google drive this morning), the formula above worked, and displayed the correct description in cell D24 for what was entered in C24. That particular part number was an "AF" valve. Anyway... in wanting to test the formula out, to see if it'd work with one of the other types of valves, I entered a different part number into the sheet, which was a DP type valve, so it should've referenced the DP__MP table for the description. When I entered that part number in, cell D24 returned #REF!.

In any event... following your instructions above, I did create the additional table, it's all the way to the right on the sheet titled "ordering chart". I've uploaded my latest revision of this sheet back to the google drive, and I added some notes to the bottom of it. Here's a link: https://drive.google.com/file/d/0B7jlEtCeshT0OUhKQ3ZKczJlTzg/view?usp=sharing

 

[gaz_chops]

It is working fine, but your new table is incorrect! It shows DP__MP, but your named range is MP___DP? Letters are wrong way round and it should have 3 _ underscores not 2?

Fix the table and test again, think you'll find my formula works OK.

Gaz

 

[tsimpson]

Thank you, I'll check it tomorrow morning when I'm back at the office.

As a side-note, part of what's confusing me with this new formula, which perhaps you can help me understand, is what in this formula tells it which table to reference? It seems we're only referencing the new table you had me add today (which is only half of the solution), but I can't figure out what ties it to the 3 actual tables with the long lists of potential codes and such.

I believe you that it's in there, I'm just having difficulty understanding what I"m doing here. if I understand what we're actually doing with this formula, it'll help me retain it better and be able to figure it out next time.

 

[gaz_chops]

The Indirect function allows you to use the cell contents as a reference, so the Vlookup inside the indirect function, returns the named range from your table.
e.g. the formula below
=VLOOKUP(C24,INDIRECT(VLOOKUP(C18,'Ordering Chart'!Z1:AA12,2,FALSE)),2,FALSE)
(based on part no. MIHLC8X-G09-34), looks for MI in your new table and returns MP___DP so it is in this case doing
=VLOOKUP(C24,MP___DP,2,FALSE)

Hope that makes sense.

Gaz

 

[tsimpson]

Thanks Gaz, it worked like a charm! Now I just need to add the IFERROR function to that cell, so it doesn't return #N/A on part numbers with only 9 characters, but I'm pretty sure I can figure that out. Thanks again for the help, if it weren't for that whole ocean thing, I'd buy you a beer, haha.

 

[gaz_chops]

You're welcome mate, beer would be good on a Friday afternoon, but a Thanks is nearly as good

P.s. hope the explanation made sense.