Help please

[CyannideHigh]

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"?

 

[Trebor76]

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]

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]

Ignore my last message i managed to figure it out XD Thanks again robert for the push in the right direction

 

[Trebor76]

Thanks again robert for the push in the right direction

You're welcome. I'm glad it's all sorted now

Robert