vlookup and if

[marlen0804]

Hi Guys

I have a tricky situation. I have two sheets and I am trying to vlookup at job number in one sheet to match the job number in the other sheet only if the location in sheet 2 matches the location in sheet one. I would really, really appreciate any help.

example:

sheet1

A Location
job code
C5 102
A7 400001
A8 500002
A9 600003

Sheet 2

Location salary
A1 102 B1 30K
A2 103 B2 50K
A4 104 B3 40K
A5 105 B4 60K

And if someone can help me I can actually send the sheets. Thanks again very much

 

[cgcamal]

Hi marlen0804,

May you show or upload sample input file and show desired output, in this way any body will be able to undertand and help easier.

You can use MrExcel HTML Maker to show it.

Regards

 

[marlen0804]

besies HTML maker how do I upload a sample file. Can I email you

 

[cgcamal]

With HTML Maker you could select a range of cells and copy and paste it into the post. You can upload a file using a free file sharing services
and put here the link to download or using skydrive or something. If you have trouble with that, tell me to see another option.

Regards

 

[marlen0804]

ok hopefully this works if not please let me know if i can email it to you and maybe as favor you can post it for me.

<p>sheet 1</p>
<p>l want to vlookup 500002 in sheet 2, only if the location 102 is in sheet 2 to return 50k</p>
<table style="width: 345px;" border="0" cellspacing="0" cellpadding="0">
<colgroup span="1"><col span="1" width="79"></col><col span="1" width="190"></col><col span="1" width="12"></col><col span="1" width="64"></col></colgroup>
<tbody>
<tr height="20">
<td class="xl72" width="79" height="20"> </td>
<td class="xl73" width="190"> </td>
<td class="xl72" width="12"> 102</td>
<td class="xl74" width="64" align="right">103</td>
</tr>
<tr height="20">
<td class="xl72" height="20">500002</td>
<td class="xl73">Assistant General Manager</td>
<td class="xl72"> </td>
<td class="xl74"> </td>
</tr>
<tr height="20">
<td class="xl72" height="20">500008</td>
<td class="xl73">Manager On Duty - Front Desk</td>
<td class="xl72"> </td>
<td class="xl74"> </td>
</tr>
</tbody>
</table>
<p> </p>
<p>sheet2</p>
<table style="width: 195px; height: 40px;" border="0">
<tbody>
<tr>
<td>102</td>
<td>500002</td>
<td>50k</td>
</tr>
<tr>
<td>103</td>
<td>500002</td>
<td>60k</td>
</tr>
</tbody>
</table>
<p> </p>
<p> </p>

 

[cgcamal]

Hi Marlen,

I'm not sure if it is exactly what you need, but try with the following array formula in D3 and copy down.
(Enter with Ctrl+Shift+Enter)

Excel Workbook
	A	B	C	D
1	Header1	Header2	Header3	Result
2		102	103
3	500002	Assistant	General Manager	50k
4	500008	Manager On Duty	Front Desk	Value is not in Sheet2
In Sheet1

Excel Workbook
	A	B	C
1	102	500002	50k
2	103	500002	60k
In Sheet2

Excel Workbook
Cell	Formula
D3	=IF(Sheet2!$A$1:$A$100=B$2,IFERROR(VLOOKUP(A3,Sheet2!B:C,2,FALSE),"Value is not in Sheet2"))
D4	=IF(Sheet2!$A$1:$A$100=B$2,IFERROR(VLOOKUP(A4,Sheet2!B:C,2,FALSE),"Value is not in Sheet2"))
Array Formulas in Sheet1

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter these manually yourself

Hope this helps.

Regards.