CyannideHigh

New Member
Joined
Dec 14, 2018
Messages
15
Hey guys (and girls),

this is my first post XD... first off sorry if it's in the wrong place and second thanks in advance if anyone can help.

=IF($A2=Items!$A$2,Items!$B$2,0)

This is the base of my formula, as you can see i'm trying to see if "sheet As" A2 matchs "Sheets Bs" A2 and returning a value if true... problem i have is "Sheet B" is a long list of things that need cross-referencing and now my formula looks like this...

=IF($A2=Items!$A$2,Items!$B$2,IF($A2=Items!$A$3,Items!$B$3,IF($A2=Items!$A$4,Items!$B$4,IF($A2=Items!$A$5,Items!$B$5,IF($A2=Items!$A$6,Items!$B$6,IF($A2=Items!$A$7,Items!$B$7,IF($A2=Items!$A$8,Items!$B$8,IF($A2=Items!$A$9,Items!$B$9,IF($A2=Items!$A$10,Items!$B$10,IF($A2=Items!$A$11,Items!$B$11,IF($A2=Items!$A$12,Items!$B$12,IF($A2=Items!$A$13,Items!$B$13,IF($A2=Items!$A$14,Items!$B$14,IF($A2=Items!$A$15,Items!$B$15,IF($A2=Items!$A$16,Items!$B$16,IF($A2=Items!$A$17,Items!$B$17,IF($A2=Items!$A$18,Items!$B$18,IF($A2=Items!$A$19,Items!$B$19,IF($A2=Items!$A$20,Items!$B$20,0)))))))))))))))))))

if ANYONE know an easier way to do this and can help me out,
I would eternally gratful... i know there is a way i just can't remember
Ironically i use the above formula or something similar regularly

Thank you again

Side note, anyone know how many "IF" functions i can string this way before excel says "nope i'm out"?
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,676
Hi CyannideHigh,

Welcome to MrExcel!!

Seems like you need a VLOOKUP formula like so:

=VLOOKUP($A2,Items!$A:$B,2,FALSE)

Note if there is no exact match #N/A will be returned.

From Excel 2007 I believe you can have 32 nested IF statements but in my humble opinion you shouldn't need to get anywhere near that.

Regards,

Robert
 

CyannideHigh

New Member
Joined
Dec 14, 2018
Messages
15
Hey there and thank you for the response,

Unfortunatly i have given this a shot and had a "jiggle" around with it and i can't get it to work :'(
and awesome to know on the nesting (got some way to go yet XD)

Thank you
 

CyannideHigh

New Member
Joined
Dec 14, 2018
Messages
15
Ignore my last message i managed to figure it out XD Thanks again robert for the push in the right direction
 

Watch MrExcel Video

Forum statistics

Threads
1,109,149
Messages
5,527,098
Members
409,745
Latest member
CharlesAlsop

This Week's Hot Topics

Top