• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

Excel - Array Vlookup

I have an excel spreadsheet with one worksheet called "Material" and another worksheet called "Reference".  Based of values from "Material" worksheet, it would look at the "Reference" worksheet to see which chart to use and what the "Polishing Lb" is.  Is it even possible to use formulas to do an array vlookup?

In this example, my worksheet "Material" has all these columns with values.  The "Polishing LB" is what I am trying to do a lookup against.
Material.jpg
So the 3 charts is based of the Width and Length, on which chart to use.
Chart 1 - Width is between 36 and 60, and length is up to 160.
Chart 2 - Width is between 48 to 60, and length is over 160.
Chart 3 - Width is over 72 and length is over 160.

Reference.jpg
Once the chart is determine than the Gauge, Grade, and Polish is what determine the Polishing LB.  Is it even possible to do this lookup by formula, or would a VBA script be necessary.  Can someon provide sample?

Also attaching the excel.
STEEL---POLISH-CHART.xlsm
0
holemania
Asked:
holemania
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
Rory ArchibaldCommented:
This is how I would approach it, but I'm not sure how to handle the N/A in your Polish column?

Note: I had to change the spelling of a couple of items so they match the column headers, and I assumed the Gauge should be an exact match to the values in the tables.
STEEL---POLISH-CHART.xlsm
0
 
Saqib Husain, SyedEngineerCommented:
I have come up with this monster but you have to make sure that the spellings match exactly on the two sheets. THIS IS AN ARRAY FORMULA. I have also modified your sheet as shown in green.

=IF(MAX((OFFSET(reference!$A$1,MAX(COUNTIF(F2,reference!$B$1:$B$40)*COUNTIF(F2,reference!$C$1:$C$40)*COUNTIF(G2,reference!$D$1:$D$40)*ROW(reference!$B$1:$B$40))+1,0,1,9)=D2)*(OFFSET(reference!$A$1,MAX(COUNTIF(F2,reference!$B$1:$B$40)*COUNTIF(F2,reference!$C$1:$C$40)*COUNTIF(G2,reference!$D$1:$D$40)*ROW(reference!$B$1:$B$40)),0,1,9)=E2)*COLUMN($A$1:$I$1))=0,0,INDEX(reference!$A$1:$I$40,MATCH(C2,OFFSET(reference!$A$1,MAX(COUNTIF(F2,reference!$B$1:$B$40)*COUNTIF(F2,reference!$C$1:$C$40)*COUNTIF(G2,reference!$D$1:$D$40)*ROW(reference!$B$1:$B$40))+2,0,9))+MAX(COUNTIF(F2,reference!$B$1:$B$40)*COUNTIF(F2,reference!$C$1:$C$40)*COUNTIF(G2,reference!$D$1:$D$40)*ROW(reference!$B$1:$B$40))+2,MAX((OFFSET(reference!$A$1,MAX(COUNTIF(F2,reference!$B$1:$B$40)*COUNTIF(F2,reference!$C$1:$C$40)*COUNTIF(G2,reference!$D$1:$D$40)*ROW(reference!$B$1:$B$40))+1,0,1,9)=D2)*(OFFSET(reference!$A$1,MAX(COUNTIF(F2,reference!$B$1:$B$40)*COUNTIF(F2,reference!$C$1:$C$40)*COUNTIF(G2,reference!$D$1:$D$40)*ROW(reference!$B$1:$B$40)),0,1,9)=E2)*COLUMN($A$1:$I$1))))
STEEL---POLISH-CHART.xlsm
0
 
Robberbaron (robr)Commented:
heres my version.

uses some hidden cells to hold the calc about what Chart, Polish type and Grade is to be used.
then uses Vlookup into the correct table.

but as RoryA noted, you havent given details on how to properly handle the NA polish type.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Saqib Husain, SyedEngineerCommented:
File?
0
 
Robberbaron (robr)Commented:
0
 
holemaniaAuthor Commented:
Thanks all.  I will take a look at each propose solution.  The "N/A", if there's nothing I want to show a zero.
0
 
holemaniaAuthor Commented:
Thanks all.  I had tried all 3 and all 3 worked well.  Rory's solution was probably the easiest for me to follow and will use his solution, but will divide points amongst all 3.  However, I do have a question to Rory.  If it's N/A, can the formula be tweaked to show a zero instead?
0
 
Rory ArchibaldCommented:
Will the N/A only ever appear in one column, or could it be in any of the three criteria columns?
0
 
holemaniaAuthor Commented:
As far as I can tell, it's only in the Polish column.  However, if it's not able to find the value, I also want to list as zero.  So I guess it would not matter which column, long as it can't find a match, it would show 0 instead of N/A as the result.
0
 
Rory ArchibaldCommented:
In that case:

=IFERROR(INDEX(CHOOSE(I2,reference!$B$4:$I$12,reference!$B$18:$I$26,reference!$B$32:$I$40),MATCH(C2,reference!$A$4:$A$12,0),MATCH(D2&E2,reference!$B$3:$I$3&reference!$B$2:$I$2,0)),0)

still array-entered using Ctrl+Shift+Enter.
0
 
holemaniaAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now