Forum Discussion
Excel
Hi Andrea,
You may pick-up descriptions with formula like
=IFNA(INDEX(<validation description range>,MATCH(<CN>,<validation CN range>,0)),"")
concrete formula depends on how your actual data is structured
Sergei,
If I have the cost number report without the descriptions in the 1st sheet and then the cost numbers with the descriptions in a 2nd sheet how would I use that formula. I am not a pro with regards to Excel. I have attached the report for you to see.
Hi Andrea,
With your data the formula will be
=IFNA(INDEX(Description!B:B, MATCH($D2,Description!A:A,0)),"")
MATCH finds the position (row number) of the Cost Number cell in Description sheet with the value which is equal to the value of cell D2
INDEX takes that position and returns the Description from the same row (next cell)
IFNA returns empty string of Cost Number is not found in Description sheet
Eneter the formula in E2 and drag it down till end of you range (or select entire range includes E2 and press Ctrl+D).
Just in case, more suitable if you work with Excel Tables. Using them you don't depend on the size of your range and formulas will be automatically populated when you add/change them. I added two more worksheets with the tables to illustrate. The formula will be
=IFNA(INDEX(Description[Description], MATCH([@[Cost Number]],Description[Cost Number],0)),"")
Please see attached.
Sergei,
Thank you so much for that - you didn't actually have to complete the whole thing but I appreciate it very much. :)