Excel find actual values

[rrmcguire]

I have three columns of values

1st column is all order numbers
2nd Column is all weights
3rd column is the orders I need(for a pivot table)

The first column has all the orders and the second has the weights for these orders but I need to somehow from the weights in the second column match them to the correct orders listed in the 3rd column

 

[SkipVought]

Hi,

????

Please post an example of your table and the results you expect.

Do you mean, I'm just guessing from the vague information you have posted, that columns A & B have a MASTER table of OrderIDs & Weights and some other column like D has a subset of OrderIDs for which you need corresponding weights in the adjacent column, via a lookup formula?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[rrmcguire]

example

Column A(all the orders)

​

Column B(all the weights)

Column C(the orders I'm using)

1 200 3
2 300 5
3 400 6
4 500 8
5 600
6 700
7 800
8 900


So I need to produce a fourth column which shows in this example the weights for order 3,5,6, and 8.....but there are about 600 orders which I'm needing the weights for which are in column B​

 

[SkipVought]

????

There is NO visible column C, probably because you are not using TGML PRE tags and you have not provided the results required in your 4th non-existent column!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[rrmcguire]

Column C is the orders I'm needing the weights on

3
5
6
8

 

[SkipVought]

In your example, your OrderIDs look like ROW NUMBERS. Very very misleading, since you did not have Headings as all proper table ought to have.

Furthermore, weights, 3, 5, 6, 8 are not related to your master lookup table and ought not to be in that table.


So making your master table a Structured Table named tORDS...
[pre]
OrderID Weight
1 200
2 300
3 400
4 500
5 600
6 700
7 800
8 900
[/pre]

...your order subset in column E
[pre]
OrderID Weight
3 400 =INDEX(tORDS[Weight],MATCH(E2,tORDS[OrderID],0),1)
5 600
6 700
8 900
[/pre]



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[rrmcguire]

I basically need to find out what are the weights for my orders in column C

The values in column C do exist in column A

 

[SkipVought]

Put your orders in column D, make your table a Structured Table with proper headings as per my solution will work.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Okay, here's everything in columns a:d, although it makes no sense from a table structure

[pre]
OrderID Weight OID WGT
1 200 5 600 =INDEX([Weight],MATCH([@OID],[OrderID],0),1)
2 300 6 700
3 400 8 900
4 500 #N/A
5 600 #N/A
6 700 #N/A
7 800 #N/A
8 900 #N/A
[/pre]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[rrmcguire]

ok, in my sheet I have all the orders in Column S, all the orders I'm needing the weights for in Column T, and all the weights in Column U, then I'm setting my formula in V as

=INDEX(U2,MATCH(T2,S2,0),1)

and it doesn't appear to be pulling anything


Any help you can provide would be appreciated....thanks

 

[SkipVought]

If you're table is a Structured Table, the formula is as posted in my prior post. Instead of a:d your columns are s:v.

Pretty simple!

You REALLY ought to isolate your master lookup table from your u:v columns. Best practice for Lists & Tables.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[rrmcguire]

Ok really dumb question but do I need the brackets around the cells or are those the range of cells I'm pulling from?

 

[rrmcguire]

What is the 0,1?

 

[rrmcguire]

I've attached my workbook...could you take a look at it if you have a chance...each time I run the formula the way you specified the weights for my orders come out incorrect

thank you for all your help

 

[SkipVought]

Okay, here's what I did.

1) made your table a STRUCTURED TABLE with the name tORDS
2) entered the formula
[tt]
=INDEX([[highlight #FCE94F]Weight[/highlight]],MATCH([@[My Orders]],[[highlight #FCE94F]Order Number[/highlight]],0),1)
[/tt]
Your formula is not looking at [highlight #FCE94F]ranges[/highlight]; rather single cell references.

Making a table a Structured Table is very simple and easy.
1) select in the table
2) Insert > Tables > Table ... Check, My table has headings.
3) Change the Table Name from Table1, Table2 etc to something meaningful, like tORDS (table tORDS or tORDERS)

I also demonstrated that your My Orders table ought to be decoupled from your Orders table. If you look after row 626, you'll see #N/A! errors cuz your implied tables are of different sizes.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[rrmcguire]

thanks for your help, yes they will have different sizes as I'm only trying to get the weights for those 600 or so orders

thank you

 

[SkipVought]

BTW, the only things I actually typed were...
[tt]
=in TAB
[/tt]
...and that resulted in...
[tt]
=INDEX(
[/tt]

Then I continued typing...
[tt]
tor TAB
[/tt]
...and that resulted in...
[tt]
=INDEX(tORDS
[/tt]

Then I typed an OPEN BRACKET: [
...and that resulted in a popup list of all the ranges in your table. I selected Order Number TAB, and now I have...
[tt]
=INDEX(tORDS[Order Number
[/tt]
...and I just close the brackets.

And so it goes, with a minimal of typing.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}