#### CyannideHigh

##### New Member
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"?

### 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
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
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
Ignore my last message i managed to figure it out XD Thanks again robert for the push in the right direction

#### Trebor76

##### Well-known Member
Thanks again robert for the push in the right direction

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

Robert

Replies
12
Views
188
Replies
5
Views
48
Replies
1
Views
64
Replies
1
Views
31
Replies
12
Views
220