VLOOKUP - Multiple Criteria

G

gregula82

New Member
Joined
Aug 18, 2006
Messages
8
Hi Everyone,

I was wondering whether anyone knew of a way of performing a VLOOKUP function which has multiple criteria. e.g. the lookup value would have 3 separate criteria and then you put the table array in and the column index.

Any ideas???

Thanks.

Greg.
 
Sort by date Sort by votes
@Aladin

Apologies for the repeat.

I will try to simplify my question, I am trying to populate WD1 planned hours and WD1 planned cost on sheet 2 from sheet 1

sample data on Wd1 Sheet:

[TABLE="width: 480"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Programme ID[/TD]
[TD]Project ID[/TD]
[TD]Resource Name[/TD]
[TD]Task Name[/TD]
[TD]WD1 PH[/TD]
[TD]WD1 PC[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]201[/TD]
[TD]P268[/TD]
[TD]Name 1[/TD]
[TD]T1[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]202[/TD]
[TD]P298[/TD]
[TD]Name 2[/TD]
[TD]T2[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]203[/TD]
[TD]P299[/TD]
[TD]Name 3[/TD]
[TD]T3[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]201[/TD]
[TD]P268[/TD]
[TD]Name 4[/TD]
[TD]T1[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]224[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]202[/TD]
[TD]P269[/TD]
[TD]Name 1[/TD]
[TD]T2[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]280[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]201[/TD]
[TD]P269[/TD]
[TD]Name 2[/TD]
[TD]T3[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]280[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]202[/TD]
[TD]P268[/TD]
[TD]Name 3[/TD]
[TD]T1[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]336[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]201[/TD]
[TD]P299[/TD]
[TD]Name 4[/TD]
[TD]T4[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]202[/TD]
[TD]P268[/TD]
[TD]Name 5[/TD]
[TD]T1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]201[/TD]
[TD]P298[/TD]
[TD]Name 6[/TD]
[TD]T3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]203[/TD]
[TD]P268[/TD]
[TD]Name 1[/TD]
[TD]T2[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]448[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]203[/TD]
[TD]P267[/TD]
[TD]Name 2[/TD]
[TD]T2[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]392[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]204[/TD]
[TD]P268[/TD]
[TD]Name4[/TD]
[TD]T5[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]392[/TD]
[/TR]
</tbody>[/TABLE]
Sample Data on Sheet2

[TABLE="width: 576"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Month[/TD]
[TD="class: xl65, width: 64"]Programme ID[/TD]
[TD="class: xl65, width: 64"]Project ID[/TD]
[TD="class: xl65, width: 64"]Resource Name[/TD]
[TD="class: xl65, width: 64"]Task Name[/TD]
[TD="class: xl65, width: 64"]PH[/TD]
[TD="class: xl65, width: 64"]PC[/TD]
[TD="class: xl65, width: 64"]WD1 PH[/TD]
[TD="class: xl65, width: 64"]WD1 PC[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]201[/TD]
[TD="class: xl65"]P268[/TD]
[TD="class: xl65"]Name 1[/TD]
[TD="class: xl65"]T1[/TD]
[TD="class: xl65, align: right"]14[/TD]
[TD="class: xl65, align: right"]112[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]202[/TD]
[TD="class: xl65"]P298[/TD]
[TD="class: xl65"]Name 2[/TD]
[TD="class: xl65"]T2[/TD]
[TD="class: xl65, align: right"]14[/TD]
[TD="class: xl65, align: right"]112[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]203[/TD]
[TD="class: xl65"]P299[/TD]
[TD="class: xl65"]Name 3[/TD]
[TD="class: xl65"]T3[/TD]
[TD="class: xl65, align: right"]14[/TD]
[TD="class: xl65, align: right"]112[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]201[/TD]
[TD="class: xl65"]P268[/TD]
[TD="class: xl65"]Name 4[/TD]
[TD="class: xl65"]T1[/TD]
[TD="class: xl65, align: right"]28[/TD]
[TD="class: xl65, align: right"]224[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]224[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]202[/TD]
[TD="class: xl65"]P269[/TD]
[TD="class: xl65"]Name 1[/TD]
[TD="class: xl65"]T2[/TD]
[TD="class: xl65, align: right"]35[/TD]
[TD="class: xl65, align: right"]280[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]280[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]201[/TD]
[TD="class: xl65"]P269[/TD]
[TD="class: xl65"]Name 2[/TD]
[TD="class: xl65"]T3[/TD]
[TD="class: xl65, align: right"]35[/TD]
[TD="class: xl65, align: right"]280[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]280[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]202[/TD]
[TD="class: xl65"]P268[/TD]
[TD="class: xl65"]Name 3[/TD]
[TD="class: xl65"]T1[/TD]
[TD="class: xl65, align: right"]42[/TD]
[TD="class: xl65, align: right"]336[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]336[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65, align: right"]201[/TD]
[TD="class: xl65"]P299[/TD]
[TD="class: xl65"]Name 4[/TD]
[TD="class: xl65"]T4[/TD]
[TD="class: xl65, align: right"]14[/TD]
[TD="class: xl65, align: right"]112[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]112[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65, align: right"]202[/TD]
[TD="class: xl65"]P268[/TD]
[TD="class: xl65"]Name 5[/TD]
[TD="class: xl65"]T1[/TD]
[TD="class: xl65, align: right"]7[/TD]
[TD="class: xl65, align: right"]56[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]201[/TD]
[TD="class: xl65"]P298[/TD]
[TD="class: xl65"]Name 6[/TD]
[TD="class: xl65"]T3[/TD]
[TD="class: xl65, align: right"]7[/TD]
[TD="class: xl65, align: right"]56[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]203[/TD]
[TD="class: xl65"]P268[/TD]
[TD="class: xl65"]Name 1[/TD]
[TD="class: xl65"]T2[/TD]
[TD="class: xl65, align: right"]56[/TD]
[TD="class: xl65, align: right"]448[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]448[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: right"]203[/TD]
[TD="class: xl65"]P267[/TD]
[TD="class: xl65"]Name 2[/TD]
[TD="class: xl65"]T2[/TD]
[TD="class: xl65, align: right"]49[/TD]
[TD="class: xl65, align: right"]392[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]392[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: right"]204[/TD]
[TD="class: xl65"]P268[/TD]
[TD="class: xl65"]Name 4[/TD]
[TD="class: xl65"]T5[/TD]
[TD="class: xl65, align: right"]49[/TD]
[TD="class: xl65, align: right"]392[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]392[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: right"]229[/TD]
[TD="class: xl65"]P290[/TD]
[TD="class: xl65"]Name 7[/TD]
[TD="class: xl65"]T5[/TD]
[TD="class: xl65, align: right"]14[/TD]
[TD="class: xl65, align: right"]112[/TD]
[TD="class: xl65, align: center"]#N/A[/TD]
[TD="class: xl65, align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: right"]202[/TD]
[TD="class: xl65"]P298[/TD]
[TD="class: xl65"]Name 1[/TD]
[TD="class: xl65"]T4[/TD]
[TD="class: xl65, align: right"]7[/TD]
[TD="class: xl65, align: right"]56[/TD]
[TD="class: xl65, align: center"]#N/A[/TD]
[TD="class: xl65, align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 576"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

I am looking for a formula to populate column H and I with values from sheet 1 columns F and G when a match is found. if not populate #N/A.
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Hi Aladain,

Hi Aladin,

The value from the combination of the following five columns should match.

[TABLE="width: 320"]
<colgroup><col span="5" width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Month[/TD]
[TD="class: xl65, width: 64"]Programme ID[/TD]
[TD="class: xl65, width: 64"]Project ID[/TD]
[TD="class: xl65, width: 64"]Resource Name[/TD]
[TD="class: xl65, width: 64"]Task Name[/TD]
[/TR]
</tbody>[/TABLE]

In other words,
If [(Sheet1.A2=Sheet2.A2)And((Sheet1.B2=Sheet2.B2)And(Sheet1.C2=Sheet2.C2)And(Sheet1.D2=Sheet2.D2)And(Sheet1.E2=Sheet2.E2)=True]
then sheet2.H2 should have the value from Sheet1.F1 and sheet2.I2 should have the value from Sheet1.G2.

Hope I am making sense. Apologies for any confusion and thanks for your time in looking into it. Much appreciated.
 
Upvote 0
ramgouda said:
Hi Aladain,

Hi Aladin,

The value from the combination of the following five columns should match.

[TABLE="width: 320"]
<TBODY>[TR]
[TD="class: xl65, width: 64"]Month
[/TD]
[TD="class: xl65, width: 64"]Programme ID
[/TD]
[TD="class: xl65, width: 64"]Project ID
[/TD]
[TD="class: xl65, width: 64"]Resource Name
[/TD]
[TD="class: xl65, width: 64"]Task Name
[/TD]
[/TR]
</TBODY>[/TABLE]

In other words,
If [(Sheet1.A2=Sheet2.A2)And((Sheet1.B2=Sheet2.B2)And(Sheet1.C2=Sheet2.C2)And(Sheet1.D2=Sheet2.D2)And(Sheet1.E2=Sheet2.E2)=True]
then sheet2.H2 should have the value from Sheet1.F1 and sheet2.I2 should have the value from Sheet1.G2.

Hope I am making sense. Apologies for any confusion and thanks for your time in looking into it. Much appreciated.
Click to expand...

Wd1, H2, copied down:

=A2&"|"&B2&"|"&C2&"|"&D2&"|"&E2

Sheet2, H2, copied across, and down:

=INDEX(Wd1!F:F,MATCH(A$2&"|"&$B2&"|"&$C2&"|"&$D2&"|"&$E2,Wd1!$H:$H,0))
 
Upvote 0
Another way:

Layout

[TABLE="width: 339"]
<colgroup><col width="38" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1389;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <col width="53" style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;" span="2"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;"> <tbody>[TR]
[TD="width: 38, bgcolor: transparent"]Month[/TD]
[TD="width: 74, bgcolor: transparent"]Programme ID[/TD]
[TD="width: 53, bgcolor: transparent"]Project ID[/TD]
[TD="width: 82, bgcolor: transparent"]Resource Name[/TD]
[TD="width: 58, bgcolor: transparent"]Task Name[/TD]
[TD="width: 27, bgcolor: transparent"]PH[/TD]
[TD="width: 27, bgcolor: transparent"]PC[/TD]
[TD="width: 47, bgcolor: transparent"]WD1 PH[/TD]
[TD="width: 44, bgcolor: transparent"]WD1 PC[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]201[/TD]
[TD="bgcolor: transparent"]P268[/TD]
[TD="bgcolor: transparent"]Name 1[/TD]
[TD="bgcolor: transparent"]T1[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]112[/TD]
[TD="bgcolor: yellow, align: right"]14[/TD]
[TD="bgcolor: yellow, align: right"]112[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]202[/TD]
[TD="bgcolor: transparent"]P298[/TD]
[TD="bgcolor: transparent"]Name 2[/TD]
[TD="bgcolor: transparent"]T2[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]112[/TD]
[TD="bgcolor: yellow"] [/TD]
[TD="bgcolor: yellow, align: right"]112[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]203[/TD]
[TD="bgcolor: transparent"]P299[/TD]
[TD="bgcolor: transparent"]Name 3[/TD]
[TD="bgcolor: transparent"]T3[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]112[/TD]
[TD="bgcolor: yellow, align: right"]14[/TD]
[TD="bgcolor: yellow, align: right"]112[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]201[/TD]
[TD="bgcolor: transparent"]P268[/TD]
[TD="bgcolor: transparent"]Name 4[/TD]
[TD="bgcolor: transparent"]T1[/TD]
[TD="bgcolor: transparent, align: right"]28[/TD]
[TD="bgcolor: transparent, align: right"]224[/TD]
[TD="bgcolor: yellow, align: right"]28[/TD]
[TD="bgcolor: yellow, align: right"]224[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]202[/TD]
[TD="bgcolor: transparent"]P269[/TD]
[TD="bgcolor: transparent"]Name 1[/TD]
[TD="bgcolor: transparent"]T2[/TD]
[TD="bgcolor: transparent, align: right"]35[/TD]
[TD="bgcolor: transparent, align: right"]280[/TD]
[TD="bgcolor: yellow, align: right"]35[/TD]
[TD="bgcolor: yellow, align: right"]280[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]201[/TD]
[TD="bgcolor: transparent"]P269[/TD]
[TD="bgcolor: transparent"]Name 2[/TD]
[TD="bgcolor: transparent"]T3[/TD]
[TD="bgcolor: transparent, align: right"]35[/TD]
[TD="bgcolor: transparent, align: right"]280[/TD]
[TD="bgcolor: yellow, align: right"]35[/TD]
[TD="bgcolor: yellow, align: right"]280[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]202[/TD]
[TD="bgcolor: transparent"]P268[/TD]
[TD="bgcolor: transparent"]Name 3[/TD]
[TD="bgcolor: transparent"]T1[/TD]
[TD="bgcolor: transparent, align: right"]42[/TD]
[TD="bgcolor: transparent, align: right"]336[/TD]
[TD="bgcolor: yellow, align: right"]42[/TD]
[TD="bgcolor: yellow, align: right"]336[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]201[/TD]
[TD="bgcolor: transparent"]P299[/TD]
[TD="bgcolor: transparent"]Name 4[/TD]
[TD="bgcolor: transparent"]T4[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]112[/TD]
[TD="bgcolor: yellow, align: right"]14[/TD]
[TD="bgcolor: yellow, align: right"]112[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]202[/TD]
[TD="bgcolor: transparent"]P268[/TD]
[TD="bgcolor: transparent"]Name 5[/TD]
[TD="bgcolor: transparent"]T1[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]56[/TD]
[TD="bgcolor: yellow, align: right"]7[/TD]
[TD="bgcolor: yellow, align: right"]56[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]201[/TD]
[TD="bgcolor: transparent"]P298[/TD]
[TD="bgcolor: transparent"]Name 6[/TD]
[TD="bgcolor: transparent"]T3[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]56[/TD]
[TD="bgcolor: yellow, align: right"]7[/TD]
[TD="bgcolor: yellow, align: right"]56[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]203[/TD]
[TD="bgcolor: transparent"]P268[/TD]
[TD="bgcolor: transparent"]Name 1[/TD]
[TD="bgcolor: transparent"]T2[/TD]
[TD="bgcolor: transparent, align: right"]56[/TD]
[TD="bgcolor: transparent, align: right"]448[/TD]
[TD="bgcolor: yellow, align: right"]56[/TD]
[TD="bgcolor: yellow, align: right"]448[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]203[/TD]
[TD="bgcolor: transparent"]P267[/TD]
[TD="bgcolor: transparent"]Name 2[/TD]
[TD="bgcolor: transparent"]T2[/TD]
[TD="bgcolor: transparent, align: right"]49[/TD]
[TD="bgcolor: transparent, align: right"]392[/TD]
[TD="bgcolor: yellow, align: right"]49[/TD]
[TD="bgcolor: yellow, align: right"]392[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]204[/TD]
[TD="bgcolor: transparent"]P268[/TD]
[TD="bgcolor: transparent"]Name 4[/TD]
[TD="bgcolor: transparent"]T5[/TD]
[TD="bgcolor: transparent, align: right"]49[/TD]
[TD="bgcolor: transparent, align: right"]392[/TD]
[TD="bgcolor: yellow, align: center"]#N/D[/TD]
[TD="bgcolor: yellow, align: center"]#N/D[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]229[/TD]
[TD="bgcolor: transparent"]P290[/TD]
[TD="bgcolor: transparent"]Name 7[/TD]
[TD="bgcolor: transparent"]T5[/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent, align: right"]112[/TD]
[TD="bgcolor: yellow, align: center"]#N/D[/TD]
[TD="bgcolor: yellow, align: center"]#N/D[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]202[/TD]
[TD="bgcolor: transparent"]P298[/TD]
[TD="bgcolor: transparent"]Name 1[/TD]
[TD="bgcolor: transparent"]T4[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]56[/TD]
[TD="bgcolor: yellow, align: center"]#N/D[/TD]
[TD="bgcolor: yellow, align: center"]#N/D[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]******[/TD]
[TD="bgcolor: transparent"]*************[/TD]
[TD="bgcolor: transparent"]*********[/TD]
[TD="bgcolor: transparent"]***************[/TD]
[TD="bgcolor: transparent"]**********[/TD]
[TD="bgcolor: transparent"]****[/TD]
[TD="bgcolor: transparent"]****[/TD]
[TD="bgcolor: transparent"]********[/TD]
[TD="bgcolor: transparent"]*******[/TD]
[/TR]
</tbody>[/TABLE]

Formula

Code: 
In H2 - use Ctrl+Shift+Enter to enter the formula

=VLOOKUP($E2,IF('Wd1'!$A$2:$A$14=$A2,IF('Wd1'!$B$2:$B$14=$B2,IF('Wd1'!$C$2:$C$14=$C2,IF('Wd1'!$D$2:$D$14=$D2,'Wd1'!$E$2:$G$14)))),MATCH(H$1,'Wd1'!$E$1:$G$1,0),0)

And copy to the right and down.

Markmzz
 
Upvote 0
Not sure if this is the same or if I need to post this in another thread. This is really killing me.

I have a spreadsheet that I use VLookup to find some data. The name for the data can be STT1RM1 or it can be STT1TRM2 and the column (depending on which one exists) that I will return is column 4.

Below is my formula. Please help me understand what I am missing here.

=IF(ISNA(VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,FALSE)),“”,VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,FALSE))

Thank you! Great forum!
 
Upvote 0
ScottVP said:
Not sure if this is the same or if I need to post this in another thread. This is really killing me.

I have a spreadsheet that I use VLookup to find some data. The name for the data can be STT1RM1 or it can be STT1TRM2 and the column (depending on which one exists) that I will return is column 4.

Below is my formula. Please help me understand what I am missing here.

=IF(ISNA(VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,FALSE)),“”,VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,FALSE))

Thank you! Great forum!
Click to expand...

If VLOOKUP is expected to return a text value when successful...
Rich (BB code): 
=IFERROR(LOOKUP(REPT("z",255),
  CHOOSE({1,2},VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0),
   VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0))),"")
If VLOOKUP is expected to return a number when successful...
Rich (BB code): 
=IFERROR(LOOKUP(9.99999999999999E+307,
  CHOOSE({1,2},VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0),
   VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0))),"")

Hope this helps.
 
Upvote 0
ScottVP said:
Not sure if this is the same or if I need to post this in another thread. This is really killing me.

I have a spreadsheet that I use VLookup to find some data. The name for the data can be STT1RM1 or it can be STT1TRM2 and the column (depending on which one exists) that I will return is column 4.

Below is my formula. Please help me understand what I am missing here.

=IF(ISNA(VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,FALSE)),“”,VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,FALSE))

Thank you! Great forum!
Click to expand...

Hi ScottVP,

If I understand correctly what you want, maybe this can helps:

Code: 
=IFERROR(VLOOKUP("STT1RM1",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0),
IFERROR(VLOOKUP("STT1RM2",'C:\INV\[Fix.xlsx]servers'!$A$2:$I$1409,4,0),""))

Markmzz
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Matching multiple criteria that includes comma separated value
Replies
2
Views
102
Rokcmd
R
A
  • Solved
Index Match With Multiple Criteria
Replies
3
Views
240
acool
A
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
561
Peter_SSs
Peter_SSs
J
  • Solved
Filter function with multiple and|or criteria not working
Replies
3
Views
447
Peter_SSs
Peter_SSs
D
  • Solved
Matching TODAY() using VLOOKUP
Replies
7
Views
163
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,919
Messages
6,175,368
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top