Specification Comparison Table from Different Worksheets

OK - so I had a look. Guess what? It's EXACTLY what I said it might be back in post #30 that YOU said you'd checked, but clearly not!!!

There are trailing spaces in the following cells on the COMPARE tab: B12, A37, A40, A42, A49 and A54. Get rid of them and the formula will work fine.
 
Hi Ali, yes I've been trialing different things before coming back to the forum, last i check i created 5 excel files without success. Thanks for the help!
 
What are trailing spaces? I've tried to hit delete on all the cells affected?
 
If you place your cursor into those cells, you will see that there is a space after the final letter. You will need to delete the space in each case, then the lookup formula will find a match.
 
Doh! I've been clicking at the wrong cells. It finally worked... just 1 last thing, values return for row 28-32 is not what is expected. I'm expecting results return of X, Y, Z, T, R. Instead i'm getting Z, T, R etc.
 
In D28 copied down to D32:

=IFERROR(INDEX(Composite[Value],AGGREGATE(15,6,ROW(Composite[Brand & Model])-1/((Composite[Brand & Model]=D$1)*(Composite[Parameters]=$A$28)),ROWS($1:1))),"")

Now you can select cells D28 to D32 – CTRL+C to copy – select cells E28 to H32 – right-click and click on Paste Formulas (the third clipboard icon with fx on it).
 
Hi Ali. Yes. I'm over the moon with the results. It's really fascinating seeing this coming to life. Thank you so much for your help in this. Really really appreciate your tremendous help in this.

Thank you!
 
Thanks for letting me know - there's nothing more frustrating than being left hanging, especially when one has made such a big effort. ;)

Have a long and happy life with PowerQuery!
 
Yea, sorry for not replying sooner. I'm in NZ so time difference is quite stark, as well as works keep coming in. Thanks again Ali!
 
Back
Top