VLookup/Match ????

[JOANNMU]

I am trying to lookup data in Excel 2010 on two spreadsheets. Sheet 1, column N is my material number, Column V is my delivery time. I have like data in sheet 2 with delivery time on column E. I need to populate column W in sheet 1 with the data in column E on sheet 2. I tried match, lookup, etc. and nothing seems to work.....******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;"></object>

 

[JOANNMU]

I appreciate all of your assistance with this.

Would I be able to use the same formula structure in if W was looking for net price between the columns based off of the item number?
******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;"></object>

 

[jarjarbingie]

=IFERROR(OFFSET(Sheet2!$R$1,MATCH($N2,Sheet2!$E$2:$E$100,0),0),"")

just change Sheet2!$R$1 to the correct column..

 

[JOANNMU]

Thanks Again! I tried changing the formula to match the correct column, but I am not receiving exact matches now. I am receiving either blank or a match or an off number....I tried this formula in three other columns with the same result. No error messages, just skewed data... I am sure there must be something wrong with one of the datasets. ******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;"></object>

 

[Aladin Akyurek]

The example is in the following link.....

https://www.dropbox.com/s/6r2tdtinxsrzsfo/Example.xlsx
[...]
</OBJECT>

This is how to do it properly...

Sheet1, N1:W5, with headers in row 1.

[TABLE="width: 512"]
<COLGROUP><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4224" width=132><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1203" width=38><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1152" width=36><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1126" width=35><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1203" width=38><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1075" width=34><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1024" width=32><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1228" width=38><COL style="WIDTH: 112pt; mso-width-source: userset; mso-width-alt: 4787" width=150><COL style="WIDTH: 114pt; mso-width-source: userset; mso-width-alt: 4864" width=152><TBODY>[TR]
[TD="class: xl65, width: 132, bgcolor: transparent"]Item Number[/TD]
[TD="class: xl65, width: 38, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 36, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 35, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 38, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 34, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 32, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 38, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 150, bgcolor: transparent"]Delivery Time[/TD]
[TD="class: xl65, width: 152, bgcolor: transparent"]Delivery Time[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]34543[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]54334[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]66543[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3321222[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

Sheet2, E1:R5...

[TABLE="width: 486"]
<TBODY>[TR]
[TD="class: xl65, width: 90, bgcolor: transparent"]Item #
[/TD]
[TD="class: xl65, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 34, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 33, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 35, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 34, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 34, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 32, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 34, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 38, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 39, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 41, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 42, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 126, bgcolor: transparent"]Delivery Time
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]34543
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]22321
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]45676
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]44565
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14
[/TD]
[/TR]
</TBODY>[/TABLE]

This done with the Borders-Copy-Paste method and the only thing that is missing the expected actual value on Sheet1, W2:W5.

Looks like you want:

=IFERROR(INDEX(Sheet2!$R$2:$R$5,MATCH($N2,Sheet2!$E$2:$E$5,0)),"Not found")

in W2 on Sheet1, which must be copied down.