Complicated VLOOKUP, MATCH, IF's, and INDEX question...

I

idriveatincan

New Member
Joined
Nov 20, 2017
Messages
3
I've been spending 3+ days trying multiple strains of excel formulas to solve my conundrum and need some help.

I am trying to populate quantities by location and part number on a separate excel tab. For example:

TAB 1: **DATA SAMPLE**

A B C
# Item Number Location On-Hand
1 AMP-RBLM BGB21 448
2 AMP-RBLM WF5000 1040
3 AMP-VHB1P WITOP 620
4 B011-PP WA2301 46
5 B011-PP WA2304 1774
6 B011-6-CRP BGA23 2490
7 B011-6-CRP ST01 -2429
8 B011-6-CRP WA2301 3500
9 B011-6-8NSP BGA23 1000

TAB 2: **G0AL** (USER INPUTS ITEM NUMBER AND ADDITIONAL INFORMATION AUTO POPULATES FROM TAB1:

A B C D E F G H
# Item Number Location1 On-Hand1 Location2 On-Hand2 Location3 On-Hand3 TOTAL AVAILABLE1 AMP-RBLM BGB21 448 WF5000 1040 #N/A! #N/A! 1488
2 AMP-VHB1P WITOP 620 #N/A! #N/A! #N/A! #N/A! 620
3 B011-PP WA2301 46 WA2304 1774 #N/A! #N/A! 1820
4 B011-6-CRP BGA23 2490 ST01 -2429 WA2301 3500 3561
5 B011-6-8NSP BGA23 1000 #N/A! #N/A! #N/A! #N/A! 1000

There is about 2k lines of data in the 'TAB1' spreadsheet and there will only be about 60 lines to free form in 'TAB2'. I am only concerned with populating the free-form in 'TAB2' from the information compiled in 'TAB1'.

The original thought was to VLOOKUP the part number and then pull the quantities from the location tab as classified in consecutive order. The next thought was to look up the part number and then be specific as to what location (ie. B*, W*, or ST01) but found that it would be too cumbersome and would require too many columns for the accounting department to review.

The goal is to be able to input a specific part number (ie. AMP-RBLM) and then have the data pertaining to that part number follow on the same line. I can auto sum the difference from all of the 'On-Hand2' columns to find the available totals and I can format the #N/A! to read as 0. What I need help with is taking the information (acquired from another source and then copy/paste into the 'TAB1') from 'TAB1' and dispersing it into each row.

(I attempted to take screen shots but this forum wouldn't allow it)

If you can help, I would greatly appreciate it!

Thank you,
Keven
 

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).
Sort by date Sort by votes
Hi,

take a look at this:


Book1
ABCDEFGH
1Location1On-Hand1Location2On-Hand2Location3On-Hand3TOTAL AVAILABLE
2AMP-RBLMWF50001040BGB21448  1488
3AMP-VHB1PWITOP620620
4B011-PPWA23041774WA2301461820
5B011-6-CRPWA23013500ST01-2429BGA2324903561
6B011-6-8NSPBGA2310001000
tab2
Cell Formulas
RangeFormula
H2=SUMPRODUCT((LEFT($B$1:$G$1,7)="On-hand")*(B2:G2<>""),B2:G2)
B2{=IFERROR(INDEX('tab1'!$B$2:$B$10,LARGE(IF('tab1'!$A$2:$A$10=$A2,ROW('tab1'!$B$2:$B$10),0),RIGHT(B$1,1))-1),"")}
C2{=IFERROR(INDEX('tab1'!$C$2:$C$10,LARGE(IF('tab1'!$A$2:$A$10=$A2,ROW('tab1'!$C$2:$C$10),0),RIGHT(B$1,1))-1),"")}
D2{=IFERROR(INDEX('tab1'!$B$2:$B$10,LARGE(IF('tab1'!$A$2:$A$10=$A2,ROW('tab1'!$B$2:$B$10),0),RIGHT(D$1,1))-1),"")}
E2{=IFERROR(INDEX('tab1'!$C$2:$C$10,LARGE(IF('tab1'!$A$2:$A$10=$A2,ROW('tab1'!$C$2:$C$10),0),RIGHT(D$1,1))-1),"")}
F2{=IFERROR(INDEX('tab1'!$B$2:$B$10,LARGE(IF('tab1'!$A$2:$A$10=$A2,ROW('tab1'!$B$2:$B$10),0),RIGHT(F$1,1))-1),"")}
G2{=IFERROR(INDEX('tab1'!$C$2:$C$10,LARGE(IF('tab1'!$A$2:$A$10=$A2,ROW('tab1'!$C$2:$C$10),0),RIGHT(F$1,1))-1),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
try.

Excel 2010[TABLE="class: grid, width: 300"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]item num
[/TD]
[TD]location
[/TD]
[TD]onhand
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]AMP-RBLM
[/TD]
[TD]BGB21
[/TD]
[TD="align: right"]448
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]AMP-RBLM
[/TD]
[TD]WF5000
[/TD]
[TD="align: right"]1040
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]AMP-VHB1P
[/TD]
[TD]WITOP
[/TD]
[TD="align: right"]620
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]B011-PP
[/TD]
[TD]WA2301
[/TD]
[TD="align: right"]46
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]B011-PP
[/TD]
[TD]WA2304
[/TD]
[TD="align: right"]1774
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD]B011-6-CRP
[/TD]
[TD]BGA23
[/TD]
[TD="align: right"]2490
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD]B011-6-CRP
[/TD]
[TD]ST01
[/TD]
[TD="align: right"]-2429
[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD]B011-6-CRP
[/TD]
[TD]WA2301
[/TD]
[TD="align: right"]3500
[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD]B011-6-8NSP
[/TD]
[TD]BGA23
[/TD]
[TD="align: right"]1000
[/TD]
[/TR]
</tbody>[/TABLE]
Sheet1​



Excel 2010[TABLE="class: grid, width: 700"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]A
[/TH]
[TH]B
[/TH]
[TH]C
[/TH]
[TH]D
[/TH]
[TH]E
[/TH]
[TH]F
[/TH]
[TH]G
[/TH]
[TH]H
[/TH]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]item num
[/TD]
[TD]location1
[/TD]
[TD]onhand1
[/TD]
[TD]location2
[/TD]
[TD]onhand2
[/TD]
[TD]location3
[/TD]
[TD]onhand3
[/TD]
[TD]total available
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]AMP-RBLM
[/TD]
[TD]BGB21
[/TD]
[TD="align: right"]448
[/TD]
[TD]WF5000
[/TD]
[TD="align: right"]1040
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1488
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]AMP-VHB1P
[/TD]
[TD]WITOP
[/TD]
[TD="align: right"]620
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]620
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]B011-PP
[/TD]
[TD]WA2301
[/TD]
[TD="align: right"]46
[/TD]
[TD]WA2304
[/TD]
[TD="align: right"]1774
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1820
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]B011-6-CRP
[/TD]
[TD]BGA23
[/TD]
[TD="align: right"]2490
[/TD]
[TD]ST01
[/TD]
[TD="align: right"]-2429
[/TD]
[TD]WA2301
[/TD]
[TD="align: right"]3500
[/TD]
[TD="align: right"]3561
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]B011-6-8NSP
[/TD]
[TD]BGA23
[/TD]
[TD="align: right"]1000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1000
[/TD]
[/TR]
</tbody>[/TABLE]
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2
[/TH]
[TD="align: left"]=SUM(C2,E2,G2)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3
[/TH]
[TD="align: left"]=SUM(C3,E3,G3)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4
[/TH]
[TD="align: left"]=SUM(C4,E4,G4)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H5
[/TH]
[TD="align: left"]=SUM(C5,E5,G5)
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H6
[/TH]
[TD="align: left"]=SUM(C6,E6,G6)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Note since your sample shows only 3 locations I use the number in the header to get the K value for the small. If your locations go double digit then this will not work. You can replace the right function with the number.
[TABLE="class: grid, width: 85%"]
<tbody>[TR]
[TD]
Array Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D5
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E5
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F5
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G5
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B6
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D6
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E6
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F6
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G6
[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}
[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thank you, gentlemen! I will play with it later this morning and let you know how it goes. You have been a great help!

V/r
Keven

Scott T said:
try.

Excel 2010[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]item num[/TD]
[TD]location[/TD]
[TD]onhand[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]AMP-RBLM[/TD]
[TD]BGB21[/TD]
[TD="align: right"]448[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]AMP-RBLM[/TD]
[TD]WF5000[/TD]
[TD="align: right"]1040[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]AMP-VHB1P[/TD]
[TD]WITOP[/TD]
[TD="align: right"]620[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]B011-PP[/TD]
[TD]WA2301[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]B011-PP[/TD]
[TD]WA2304[/TD]
[TD="align: right"]1774[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]B011-6-CRP[/TD]
[TD]BGA23[/TD]
[TD="align: right"]2490[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]B011-6-CRP[/TD]
[TD]ST01[/TD]
[TD="align: right"]-2429[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]B011-6-CRP[/TD]
[TD]WA2301[/TD]
[TD="align: right"]3500[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]B011-6-8NSP[/TD]
[TD]BGA23[/TD]
[TD="align: right"]1000[/TD]
[/TR]
</tbody>[/TABLE]
Sheet1​



Excel 2010[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]item num[/TD]
[TD]location1[/TD]
[TD]onhand1[/TD]
[TD]location2[/TD]
[TD]onhand2[/TD]
[TD]location3[/TD]
[TD]onhand3[/TD]
[TD]total available[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]AMP-RBLM[/TD]
[TD]BGB21[/TD]
[TD="align: right"]448[/TD]
[TD]WF5000[/TD]
[TD="align: right"]1040[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1488[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]AMP-VHB1P[/TD]
[TD]WITOP[/TD]
[TD="align: right"]620[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]620[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]B011-PP[/TD]
[TD]WA2301[/TD]
[TD="align: right"]46[/TD]
[TD]WA2304[/TD]
[TD="align: right"]1774[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1820[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]B011-6-CRP[/TD]
[TD]BGA23[/TD]
[TD="align: right"]2490[/TD]
[TD]ST01[/TD]
[TD="align: right"]-2429[/TD]
[TD]WA2301[/TD]
[TD="align: right"]3500[/TD]
[TD="align: right"]3561[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]B011-6-8NSP[/TD]
[TD]BGA23[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1000[/TD]
[/TR]
</tbody>[/TABLE]
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10"]H2[/TH]
[TD="align: left"]=SUM(C2,E2,G2)[/TD]
[/TR]
[TR]
[TH="width: 10"]H3[/TH]
[TD="align: left"]=SUM(C3,E3,G3)[/TD]
[/TR]
[TR]
[TH="width: 10"]H4[/TH]
[TD="align: left"]=SUM(C4,E4,G4)[/TD]
[/TR]
[TR]
[TH="width: 10"]H5[/TH]
[TD="align: left"]=SUM(C5,E5,G5)[/TD]
[/TR]
[TR]
[TH="width: 10"]H6[/TH]
[TD="align: left"]=SUM(C6,E6,G6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Note since your sample shows only 3 locations I use the number in the header to get the K value for the small. If your locations go double digit then this will not work. You can replace the right function with the number.
[TABLE="class: grid, width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10"]B2[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]C2[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]D2[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]E2[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]F2[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]G2[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]B3[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]C3[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]D3[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]E3[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]F3[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]G3[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]B4[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]C4[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]D4[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]E4[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]F4[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]G4[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]B5[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]C5[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]D5[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]E5[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]F5[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]G5[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]B6[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]C6[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]D6[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]E6[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]F6[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10"]G6[/TH]
[TD="align: left"]{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Gentlemen,

I attempted your solutions in a new spreadsheet and had several errors:

Sheet1[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]PART NO.[/TD]
[TD]LOCATION1[/TD]
[TD]ON-HAND1[/TD]
[TD]LOCATION2[/TD]
[TD]ON-HAND2[/TD]
[TD]LOCATION3[/TD]
[TD]ON-HAND3[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]AMP-RBLM[/TD]
[TD]BGB21[/TD]
[TD="align: right"]448[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]448[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]AMP-VHB1P[/TD]
[TD]WF5000
[/TD]
[TD="align: right"]1040[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1040[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]B011-PP[/TD]
[TD]WITOP[/TD]
[TD="align: right"]620[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]620[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]B011-6-CRP[/TD]
[TD]WA2301[/TD]
[TD="align: right"]46[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]B011-6-8NSP[/TD]
[TD]WA2304[/TD]
[TD="align: right"]1774[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1774

[/TD]
[/TR]
</tbody>[/TABLE]

Instead of having the location and quantity data spread across the row, it is following down the column, skewing the data and causing the information to be displayed incorrectly.

The second solution only allowed information in the first location/on-hand cells, nothing else worked.

I also attempted to replace/free-form the part number by replacing the RBLM with the VHB1P part number. The information following it did not update. Am I missing something?

This is the goal:

Sheet1[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]PART NO.[/TD]
[TD]LOCATION1[/TD]
[TD]ON-HAND1[/TD]
[TD]LOCATION2[/TD]
[TD]ON-HAND2[/TD]
[TD]LOCATION3[/TD]
[TD]ON-HAND3[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]AMP-RBLM[/TD]
[TD]BGB21[/TD]
[TD="align: right"]448[/TD]
[TD]WF5000[/TD]
[TD="align: right"]1040[/TD]
[TD]N/A[/TD]
[TD]0[/TD]
[TD="align: right"]1488[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]AMP-VHB1P[/TD]
[TD]WITOP[/TD]
[TD="align: right"]620[/TD]
[TD]N/A[/TD]
[TD]0
[/TD]
[TD]N/A[/TD]
[TD]0[/TD]
[TD="align: right"]620[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]B011-PP[/TD]
[TD]WA2301[/TD]
[TD="align: right"]46[/TD]
[TD]WA2304[/TD]
[TD="align: right"]1774[/TD]
[TD]N/A[/TD]
[TD]0[/TD]
[TD="align: right"]1820[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]B011-6-CRP[/TD]
[TD]BGA23[/TD]
[TD="align: right"]2490[/TD]
[TD]ST01[/TD]
[TD="align: right"]-2429[/TD]
[TD]WA2301[/TD]
[TD="align: right"]3500[/TD]
[TD="align: right"]3561[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]B011-6-8NSP[/TD]
[TD]BGA23[/TD]
[TD="align: right"]1000[/TD]
[TD]N/A[/TD]
[TD]0[/TD]
[TD]N/A[/TD]
[TD]0[/TD]
[TD="align: right"]1000[/TD]
[/TR]
</tbody>[/TABLE]

If the cell returns the data as an error or #N/A!, I am able to change it to 'N/A' or 0. I don't need help with that.

Thank you, again for your help - it is greatly appreciated.

V/r
Keven
 
Upvote 0
Did you use Control+Shift+Enter for the array formulas?
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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