IF / AND within VLOOKUP formula

[Dave T]

Hello All,

I have a workbook with a list of structures on a worksheet call ‘BIS’ and on another worksheet called ‘Element material types’ is a list of their elements.

I suspect that trying to use VLOOKUP is the wrong formula and I should probably be using INDEX and MATCH, but I am not that familiar with this.

What I have been trying to do is create a LOOKUP formula in column B in the ‘BIS’ worksheet that will do a LOOKUP within the ‘Element material types’ worksheet and find the same ‘Plan No.’ and within the ELEMENT TYPES DESCRIPTION column if it is a ‘Deck’ it will return ‘slab’ from the SUB ELEMENT TYPES DESCRIPTION column.

A LOOKUP formula in column C in the ‘BIS’ worksheet that will do a LOOKUP within the ‘Element material types’ worksheet and find the same ‘Plan No.’ and within the ELEMENT TYPES DESCRIPTION column if it is a ‘Deck’ it will return ‘Concrete’ from the MATERIALS TYPES DESCRIPTION column.

A LOOKUP formula in column D in the ‘BIS’ worksheet that will do a LOOKUP within the ‘Element material types’ worksheet and find the same ‘Plan No.’ and within the ELEMENT TYPES DESCRIPTION column if it is a ‘Girder’ it will return ‘Beam’ from the SUB ELEMENT TYPES DESCRIPTION column.

A LOOKUP formula in column E in the ‘BIS’ worksheet that will do a LOOKUP within the ‘Element material types’ worksheet and find the same ‘Plan No.’ and within the ELEMENT TYPES DESCRIPTION column if it is a ‘Girder’ it will return ‘Steel’ from the MATERIALS TYPES DESCRIPTION column.

Within the attached workbook I have just typed in the expected formula results, but I have yet to work out the formulas to do this lookup.

Regards,
Dave T

[p45cal]

All array-entered:
B2:
=IFERROR(INDEX('Element material types'!$C$2:$C$24,MATCH($A2 & "Deck",'Element material types'!$A$2:$A$24 & 'Element material types'!$B$2:$B$24,0)),"")

C2:
=IFERROR(INDEX('Element material types'!$D$2:$D$24,MATCH($A2 & "Deck",'Element material types'!$A$2:$A$24 & 'Element material types'!$B$2:$B$24,0)),"")

D2:
=IFERROR(INDEX('Element material types'!$C$2:$C$24,MATCH($A2 & "Girders",'Element material types'!$A$2:$A$24 & 'Element material types'!$B$2:$B$24,0)),"")

E2:
=IFERROR(INDEX('Element material types'!$D$2:$D$24,MATCH($A2 & "Girders",'Element material types'!$A$2:$A$24 & 'Element material types'!$B$2:$B$24,0)),"")

copied down.

See attached.

[Dave T]

Wow p45cal,

This blows me away...

I am reasonably comfortable with VLOOKUP formulas and have seen quite a few posts / topics on the net about how much better and more flexible INDEX and MATCH formulas can be.
So I have been working through some of my existing VLOOKUP formulas and making them with INDEX and MATCH formulas, but for me they are not easy to look at and quickly see what they are doing.
Then you go and cap all of this off with what is still to me an even greater mystery...an 'Array' formula.

I can't thank you enough for this help, it is very much appreciated.

Regards,
Dave T

[Dave T]

Hello p45cal,

Here I was looking for a solution and coming up with what to me was becoming an overly complex solution.
I suddenly remembered a solution to another one of my posts that turned out to be much easier...

I remembered that a VLOOKUP formula will only return the first instance of a repeated number, so I did the following:

In the worksheet called 'Element material types' I added three extra columns on the right of the existing data and copied the three heading 'Element Types Description', 'Sub Element Types Description' and 'Material Types Description'.
Below each of these in row 2 I used the following formulas: =IF($A2=$A3,B3,"") and =IF($A2=$A3,C3,"") and =IF($A2=$A3,D3,"")
This now gave me all the required data on a single line (the first instance of that number).
Back on the 'BIS' worksheet I was now able to use a VLOOKUP formula as the previous formulas put all of the 'PN' repeated data against the first instance: =IF(ISERROR(VLOOKUP($A2,'Element material types'!$A$2:$I$2665,4,FALSE)),"",VLOOKUP($A2,'Element material types'!$A$2:$I$2665,4,FALSE))


Thanks for your solution anyway as it was good so and for me to work out what it was doing.

Regards,
Dave T