East Excel Forumla?

[lasimmon]

Hey guys.

Having issues with excel and it appears to be really easy but I just can’t seem to figure it out. I’m not the greatest with excel but though I’d be able to handle this...

Basically I have a table with 3 columns of data. I have another spot where there will be 2 parts of that data inputted and I want it to pull the third piece of data from the table. The issue is there are multiple data points with the same value in one column.

Blue. 2. Cow
Blue. 3. Monkey
Blue. 4. Dog
Green. 5. Cat
Green. 6. Fish
Green. 7. Horse


Basically I would have the colour and number in a table and then I want it to pull the animal name automatically.

Any help is appreciated.

[ExtraSlow]

I'd probably do it with a pile of if statements, but there is likely a better way.

[flipstah]

=IFS

Can help and then your lookup value could be a cell

[sabad66]

Make a separate sheet with a reference table that has one column showing the colour and number appended together, and the 2nd column with the animal name. For example


colour+number
animal


blue2
cow


blue3
monkey




Then on your main table, concatenate the two input fields and do a vlookup against your reference table to pull in the animal name. Assuming your table has 3 columns - A= input 1 color, B= input 2 number, C= looked up value, your formula on cell C2 would be:
=vlookup(A2&B2,<link to reference table>,2,false)

(This means look up the concatenated value against the reference table, pull in the 2nd column, false means it must be an exact match.)

then drag that down column C and voila.

Takes some upfront work to build the reference table but then after that should be pretty foolproof.

[killramos]

New Table
Column E Color
Column F Animal (create E and F by taking your original data but removing duplicates to get unique table of color and animal, sort how you like)
Column G sumifs( $B:$B, $A:$A, Column E, $C:$C, Column F)

Not exact but you get the idea. In short SUMIFS should be what you want once you build your E,F table.

Sabadd’s method probably more data scientist correct, but lookups and Concatenates seems overkill to me

[lasimmon]

Thanks guys. I did what Sabad suggested and it works. Seems weird that excel can’t match 2 columns but that’s alright.

[killramos]

It can

There are just many ways to skin a cat

[firebane]

Thanks guys. I did what Sabad suggested and it works. Seems weird that excel can’t match 2 columns but that’s alright.

match and lookup but a lot more complicated.

[blainer]

Sorry I'm late to the party, and instead of Vlookup gonna preach the beauty of Xlookup since it's easier than the reference table method mentioned. Also been burned by vlookup too many times by someone messing around with a table and the the whole thing goes to shit

=xlookup(A1&B1,A1:A6&B1:B6,C1:C6,"You did it wrong",0,1)

[sabad66]

Sorry I'm late to the party, and instead of Vlookup gonna preach the beauty of Xlookup since it's easier than the reference table method mentioned. Also been burned by vlookup too many times by someone messing around with a table and the the whole thing goes to shit

=xlookup(A1&B1,A1:A6&B1:B6,C1:C6,"You did it wrong",0,1)

Interesting! Never heard of xlookup until you mentioned it but looks like it is much more powerful than vlookup. Vlookup has worked great for me so far in my life but sometimes i have to adjust columns (i.e. moving the 'key' column to the very left), so this will make it easier. :thumbsup:

[lasimmon]

Sorry I'm late to the party, and instead of Vlookup gonna preach the beauty of Xlookup since it's easier than the reference table method mentioned. Also been burned by vlookup too many times by someone messing around with a table and the the whole thing goes to shit

=xlookup(A1&B1,A1:A6&B1:B6,C1:C6,"You did it wrong",0,1)

Wow This is exactly what I was looking for. Thanks!

[suntan]

Wait till you find out about zlookup.

[jwslam]

Wait till you find out about zHookup.
fixed

[mr2mike]

C'mon you dinosaurs...
INDEX MATCH

vlookup has also burnt me and I fixed a coworkers issues once with index match when vlookup and hlookup failed.

Never used xlookup but I assume same as index match.

[dirtsniffer]

Sounds like it was built to simplify index match
Not sure if is reliable or not.

[nicknolte]

Index Match is already simple to use.

I'd be worried about legacy compatibility issues if XLOOKUP is a new thing

[sabad66]

Yeah it’s really similar to index match. I typically use index match for my large power query data sets, but yesterday I tried xlookup instead and it was actually a bit easier to use. I think I’ll start using it more.

[mr2mike]

If I happen to forget it, I use this site.
https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/

INDEX (column to return a value from, MATCH (lookup value, column to look up against, 0)